CODEWITHSUNDEEP
mysqlsqlunion
sridhar s
sridhar s

Reputation: 149

MYSQL UNION required with Duplicate records

I have DCHDR Table with unique records as DC_No field and DCDTL which is a transaction table, which would have duplicate DC_Nos with different products and qty.

From the below MYSQL line, I am able to join, union 2 tables and the result is as per my requirement only when duplicate records in DCDTL are not identical,

i.e, Below example records are in DCDTL only 1 is displayed and the second record is not.

Example: fields in DCDTL and data - 

DC_Nos, Product_Desc, Nos, Qty  
111     Oxygen 99.99,  4,  10 
111     Oxygen 99.99,  4,  10

I know UNIQUE will remove duplicates in the table, but is there any other way to display even identical records of DCDTL Table. Pl suggest.

    SELECT DC_No, DC_date, T.DC_Nos, T.Product_Desc,  T.Nos, T.Qty
    FROM DCHDR AS H
    LEFT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos
    UNION
    SELECT DC_No, DC_date, T.DC_Nos, T.Product_Desc, T.Nos, T.Qty
    FROM DCHDR AS H
    RIGHT JOIN DCDTL AS T ON H.DC_No = T.DC_Nos

BELOW IS THE SAMPLE DATA OF BOTH THE TABLES AND THE DESIRED RESULT:

DCHDR Table:

 DC_date     DC_Nos  
 2013-01-01  111
 2013-01-02  112
 2013-01-03  113

DCDTL Table:

 DC_Nos, Product_Desc, Nos, Qty  
 111     Oxygen 99.99,  4,  10 
 111     Oxygen 99.99,  4,  10 
 111     Nitrogen,      9,  14
 112     Nitrogen,      8,  29
 113     Zero Air,      4,  15

Result required:

 DC_date     DC_Nos  Product_Desc, Nos, Qty
 2013-01-01  111     Oxygen 99.99,  4,  10
 2013-01-01  111     Oxygen 99.99,  4,  10
 2013-01-01  111     Nitrogen,      9,  14
 2013-01-02  112     Nitrogen,      8,  29
 2013-01-03  113     Zero Air,      4,  15

Upvotes: 0

Views: 115

Answers (2)

Devart
Devart

Reputation: 121902

This query will give you desired result -

SELECT * FROM dchdr
  LEFT JOIN dcdtl -- or maybe INNER JOIN
    ON dchdr.DC_Nos = dcdtl.DC_Nos

Is it what you want?

Upvotes: 1

John Woo
John Woo

Reputation: 263693

Why do you need to have UNION of LEFT and RIGHT JOIN when this can be achieved using INNER JOIN?

SELECT  a.DC_DATE, b.*
FROM    DCHDR a
        INNER JOIN DCDTL b
            ON a.DC_Nos = b.DC_Nos

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 1

Related Questions