asked on

query displaying duplicates

i am running a query against a table named label based on an order id - query 1 and query one results.

then i run a second query using the order id, with a few more tables to join to, for the same order id and it creates duplicates - query two and query two results

could someone help me out correcting this query so i get only the 3 results it should be?

any help is greatly appreciated.

thanks!

query 1

select label_id, order_id, box_nbr
from label 
where order_id = 'C245768' 

results query 1

294738	C245768	3	978331
294739	C245768	4	978331
294740	C245768	5	978331

query two

SELECT 
"Label"."Label_ID" , "RG_LabelDetailSummary1"."Box_Nbr", 
"RG_LabelDetailSummary1"."ORDER_ID"

FROM
(
	(
		(
			(
					(
					"RGMFG"."dbo"."RG_LabelDetailSummary1" "RG_LabelDetailSummary1" RIGHT OUTER JOIN "RGMFG"."dbo"."CUST_ORDER_LINE" "CUST_ORDER_LINE" ON ("RG_LabelDetailSummary1"."ORDER_ID"="CUST_ORDER_LINE"."CUST_ORDER_ID") AND ("RG_LabelDetailSummary1"."LINE_NO"="CUST_ORDER_LINE"."LINE_NO")
					)
			RIGHT OUTER JOIN "RGMFG"."dbo"."CUSTOMER_ORDER" "CUSTOMER_ORDER" ON "RG_LabelDetailSummary1"."ORDER_ID"="CUSTOMER_ORDER"."ID"
			) 
		RIGHT OUTER JOIN "RGMFG"."dbo"."RG_CUST_ORDER_BINARY" "RG_CUST_ORDER_BINARY" ON "RG_LabelDetailSummary1"."ORDER_ID"="RG_CUST_ORDER_BINARY"."CUST_ORDER_ID"
		) 
	LEFT OUTER JOIN "RGMFG"."dbo"."CUST_ADDRESS" "CUST_ADDRESS_SHIPTO" ON ("CUSTOMER_ORDER"."SHIP_TO_ADDR_NO"="CUST_ADDRESS_SHIPTO"."ADDR_NO") AND ("CUSTOMER_ORDER"."CUSTOMER_ID"="CUST_ADDRESS_SHIPTO"."CUSTOMER_ID")
	) 
LEFT OUTER JOIN "RGMFG"."dbo"."CUSTOMER" "CUSTOMER" ON "CUSTOMER_ORDER"."CUSTOMER_ID"="CUSTOMER"."ID"
) 
INNER JOIN "RGMFG"."dbo"."PART" "PART" ON "CUST_ORDER_LINE"."PART_ID"="PART"."ID" 
LEFT OUTER JOIN  "RGCERTPLAY"."dbo"."Label" "Label" ON "RG_LabelDetailSummary1"."ORDER_ID"= "Label"."Order_ID" 
WHERE  "RG_LabelDetailSummary1"."ORDER_ID"='C245768' 
ORDER BY  "Label"."Label_ID", "RG_LabelDetailSummary1"."ORDER_ID", 
results query two

294738	3	C245768
294738	4	C245768
294738	5	C245768
294739	4	C245768
294739	5	C245768
294739	3	C245768
294740	3	C245768
294740	4	C245768
294740	5	C245768

Open in new window

metropia

ASKER

the last table i join to on the second query, is table LABEL.

i know there must be an error on the way i am doing the join, because if i remove the last join (see attached) then i get the same number of results as query one.

LEFT OUTER JOIN  "RGCERTPLAY"."dbo"."Label" "Label" ON "RG_LabelDetailSummary1"."ORDER_ID"= "Label"."Order_ID"

Open in new window

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mike McCracken