could you please help me I want to see the prices with special price and when there isn't any special price I have used union all and left outer join but it does not work
please see below
SELECT T2.[ItemCode],T3.[ItemName],T2.[Price],T4.[Price] FROM [dbo].[OCRD] T0 INNER JOIN [dbo].[OPLN] T1 ON T0.ListNum = T1.ListNum INNER JOIN ITM1 T2 ON T1.ListNum = T2.PriceList INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode INNER JOIN OSPP T4 ON T3.ItemCode = T4.ItemCode WHERE T0.[CardCode] =[%0]
UNION ALL
SELECT T2.[ItemCode],T3.[ItemName],T2.[Price],T4.[Price] FROM [dbo].[OCRD] T0 INNER JOIN [dbo].[OPLN] T1 ON T0.ListNum = T1.ListNum INNER JOIN ITM1 T2 ON T1.ListNum = T2.PriceList INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode LEFT OUTER JOIN OSPP T4 ON T3.ItemCode = T4.ItemCode WHERE T0.[CardCode] =[%0]