Query AND OR question..
My join in my query is fine, but I want the user to be able to enter search criteria:
the ItmsGroup and the WhsCode or either 1. Is this possible?
WHERE T1.ItmsGrpNam = [%0] AND T2.WhsCode = [%1] OR
T1.ItmsGrpNam = [%0] OR
T2.WhsCode = [%1]
^This is what I thought would work, but it will not work work when I try to enter both ItmsGrpNam AND WhsCode... It only works when I enter 1 of them.
Any help is appreciated, thanks
Former Member replied
Please paste your complete query..... just looking at the WHERE might not give a complete picture
But to get started, this is what you could try and base your logic using the CASE statement as demonstrated
SELECT T1.ItemCode AS 'Item No.', T1.Quantity AS 'Quantity', T1.WhsCode AS 'Warehouse Code', T0.ItmsGrpNam AS 'Group Name' FROM [dbo].[OITB] T0 INNER JOIN [dbo].[OITM] T2 ON T2.ItmsGrpCod = T0.ItmsGrpCod INNER JOIN [dbo].[RDR1] T1 ON T1.ItemCode = T2.ItemCode WHERE T0.ItmsGrpNam LIKE (CASE WHEN '[%0]' = '' THEN '%' ELSE '[%0]' END) AND T1.WhsCode LIKE (CASE WHEN '[%1]' = '' THEN '%' ELSE '[%1]' END)