Skip to Content
0

Query with Inner Join not showing complete data

Dec 06, 2016 at 02:51 PM

13

avatar image

I have the following query I have constructed. I have been checking that all the information is showing up but I find there are 40 items are not listed when I execute the query. I am not clear on how to use the left outer joins and wonder if I should change one of them.

I need the query to show me all the Items in OITM as long as the item belongs to one of the product groups I designated in the WHERE statement, even if these items don't have information on one of the other linked tables. Please let me know how I should do this.

SELECT T0."ItemCode", T0."ItemName", T0."FrgnName", T0."SuppCatNum", T0."CardCode", T1."CardName", T1."Country", T0."InvntryUom",T0."LastPurPrc", T0."LastPurCur", T0."LastPurDat", MAX(T5. "DocDate"), T0."ReorderPnt", T0."LeadTime", T0."MinOrdrQty", T0."OrdrMulti", T0."OnHand", T0."IsCommited", T0."OnOrder", T3."PymntGroup", T4."Name", T4."E_MailL"

FROM OITM T0 LEFT OUTER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode"

LEFT OUTER JOIN OCTG T3 ON T1."GroupNum" = T3."GroupNum"

LEFT OUTER JOIN OCPR T4 ON T1."CardCode" = T4."CardCode"

LEFT OUTER JOIN OINM T5 ON T0."ItemCode" = T5."ItemCode"

WHERE T0."ItmsGrpCod" = '108' OR T0."ItmsGrpCod" = '109' OR T0."ItmsGrpCod" = '110' OR T0."ItmsGrpCod" = '111' AND T1."CntctPrsn" = T4."Name"

GROUP BY T0."ItemCode", T0."ItemName", T0."FrgnName", T0."SuppCatNum", T0."CardCode", T1."CardName", T1."Country", T0."InvntryUom",T0."LastPurPrc", T0."LastPurCur", T0."LastPurDat", T0."ReorderPnt", T0."LeadTime", T0."MinOrdrQty", T0."OrdrMulti", T0."OnHand", T0."IsCommited", T0."OnOrder", T3."PymntGroup", T4."Name", T4."E_MailL"

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers