Query to Show Certain String (1 Viewer)

[Ksabai]

I Have a tbl for courier having Order Number and Reference. I have "Tea" and "Coffee" in references. I want to Show Records for "Tea" Only but Not for Order Numbers with Both "Tea" and "Coffee". Can Someone Help.

 

[theDBguy]

Hi. Can you post some sample data, so we may be able to formulate a query to satisfy your request? Thanks.

 

[Ksabai]

i have attached the screenshot of the Table

 

[Ksabai]

I want to Show 02-Order-19.

 

[arnelgp]

SELECT tbl.[OrderID], tbl.[Reference] 
FROM tblCourier 
Where 
(SELECT Count(*) From tbl AS T1 WHERE T1.[OrderID]=tbl.[OrderID])=1 
And tblCourier.[Reference]="Coffee"

 

[theDBguy]

I want to Show 02-Order-19.

Hi. Thanks. That would mean you want to show "coffee." If so, you could try the following query (based on your sample data of only three rows in it).

SELECT OrderID
FROM tblCourier
WHERE OrderID Not In(SELECT OrderID
FROM tblCourier
WHERE Reference="tea")

Hope it helps...

 

[Ksabai]

Looks like there are some null values in the SQL it returns nothing. is there any other way to solve this

 

[arnelgp]

on which field do you have Null? if on orderID:

SELECT tbl.[OrderID], tbl.[Reference] 
FROM tblCourier 
Where 
(SELECT Count(*) From tbl AS T1 WHERE T1.[OrderID]=tbl.[OrderID])=1 
And tblCourier.[Reference]="Coffee" And IsNull(OrderID)=False;