How to query?

In the following query, @.CusVenRefLike is a string entered by user, it is by default equal to '%' so that the query return all string when user enter nothing.

SELECT Voucher.VoucherID, Voucher.VoucherRef, Customer.CustomerRef
FROM Voucher
LEFT Join Customer on Voucher.CustomerID=Customer.CustomerID
WHERE Customer.CustomerRef LIKE @.CusVenRefLike

I find that the resulting dataset contain ONLY rows that Voucher.CustomerID<>-1. The reason is that when Voucher.CustomerID= -1, Customer.CustomerRef=NULL which is not included in Customer.CustomerRef LIKE '%' filter.

How can I include those row with Voucher.CustomerID=-1?

Thanks

You need to add additional constraints to your query, depending on exactly how you want it to work.

A simple approach is to use Coalesce(CustomerRef, '' ) to force the null into an empty string. This allows the match to procede. In fact, if there is no special meaning to the CustomerRef being NULL you might want to add a default to the column.

If you want to include nulls in any search, then add Or CustomerRef Is Null to the Where clause. You could also add Or (CustomerRef Is Null and @.CusVenRefLike = '%').

|||

SELECT Voucher.VoucherID, Voucher.VoucherRef, Customer.CustomerRef
FROM Voucher
LEFT Join Customer on Voucher.CustomerID=Customer.CustomerID
WHERE Customer.CustomerRef LIKE @.CusVenRefLike

or Customer.CustomerRef is null

|||

Can do it as this as well..

SELECT Voucher.VoucherID, Voucher.VoucherRef, Customer.CustomerRef
FROM Voucher
LEFT Join Customer on Voucher.CustomerID=Customer.CustomerID
WHERE isnull(Customer.CustomerRef ,'') LIKE @.CusVenRefLike