compcrk compcrk - 5 months ago 6
SQL Question

Displaying duplicates in sql query

I have the following sql query:

SELECT `PARTNER_NAME` FROM `PARTNER`
WHERE `PARTNER_CODE` IN
(SELECT `PARTNER_CODE` FROM INBOUND_ORDER WHERE `AWB_NUM` IN
( SELECT `INBOUND_ORDER_AWB_NUM` FROM DUTY_EXEMPTION
WHERE `DE_SENT_SUBMISSION`
IS NOT NULL AND `CARGO_TO_UNHRD`IS NULL) )


How can I display the duplicate records as well

Answer

Instead of using so Much inner Query use this with join:-

SELECT P.PARTNER_NAME FROM PARTNER P INNER JOIN INBOUND_ORDER IO ON P.PARTNER_CODE = IO.PARTNER_CODE INNER JOIN DUTY_EXEMPTION DE ON IO.AWB_NUM = DE.INBOUND_ORDER_AWB_NUM WHERE DE.DE_SENT_SUBMISSION IS NOT NULL AND DE.CARGO_TO_UNHRDIS NULL

Your query have unexpected behavior due to

WHERE `AWB_NUM` IN 
( SELECT `INBOUND_ORDER_AWB_NUM` FROM DUTY_EXEMPTION 
WHERE `DE_SENT_SUBMISSION`
IS NOT NULL AND `CARGO_TO_UNHRD`IS NULL) 

It will return only first record returned by

( SELECT `INBOUND_ORDER_AWB_NUM` FROM DUTY_EXEMPTION 
    WHERE `DE_SENT_SUBMISSION`
    IS NOT NULL AND `CARGO_TO_UNHRD`IS NULL)