[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

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

0
metropia
Asked:
metropia
  • 2
  • 2
2 Solutions
 
metropiaAuthor Commented:
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

0
 
LowfatspreadCommented:
i think you need the box_nr condition as well on the join to label....

however you appear to have a strange set of joins....

its normally easier if you try to just use one join type (left or right) throughout the statement...

the where condition "RG_LabelDetailSummary1"."ORDER_ID"='C245768'
is essentially making the right outer join an inner one anyway....


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"
 and label.box_nr="RG_LabelDetailSummary1"."Box_Nbr"
WHERE  "RG_LabelDetailSummary1"."ORDER_ID"='C245768'
ORDER BY  "Label"."Label_ID", "RG_LabelDetailSummary1"."ORDER_ID"
0
 
mlmccCommented:
WHy do youneed all the other tables if you are just selecting from 2 tables?

There are 3 labels and apparently 3 boxes in the order table so you end up with each label being linked to each box

Try

ON "RG_LabelDetailSummary1"."ORDER_ID"= "Label"."Order_ID"
AND   "RG_LabelDetailSummary1"."Box_Nbr"= "Label"."Box_Nbr"

mlmcc
0
 
metropiaAuthor Commented:
i removed all the other fields from the other tables to make it shorter when providing an example.
0
 
mlmccCommented:
DId you try changing the linking on the label table as shown above?

mlmcc
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now