danman280 danman280 - 3 days ago 6
MySQL Question

Query Data from 2 MySQL tables with some duplicate records

I have 2 tables that track rewards points. One is a summary table and one is a detail table. When a transaction (addition or subtraction of reward points)happens an entry is suppose to be written to both tables Unfortunately we found through a scripting error that sometimes an entry was only written to one table.

So now I need to query the database to list the transactions, without repeating the duplicates. There are not matching IDs in the table, however the DateSubmitted is a timestamp and all matching records between the 2 table share the same timestamp.

So, I have 2 queries

SELECT CustID, DateSubmitted, Type, Points
FROM `trans_summary`
WHERE CustID = '10009'


and

SELECT CustID, DateSubmitted, Type, PointTotal as Points
FROM `ptrans_detail`
WHERE CustID = '10009'
and DateSubmitted NOT IN
(SELECT DateSubmitted FROM `trans_summary`
WHERE CustID = '10009'
)


How can I combine these 2 queries to get one output?

Answer

you can use union if you need distinct result for both or union all if need also duplicated result

      SELECT CustID
          , DateSubmitted
          , Type
          , Points
              FROM `trans_summary`
                WHERE CustID = '10009'
    UNION

    SELECT CustID
    , DateSubmitted
    , Type
    , PointTotal 
        FROM `ptrans_detail` 
           WHERE CustID = '10009'
                and DateSubmitted NOT IN 
               (SELECT DateSubmitted FROM 
                 `trans_summary` 
                  WHERE CustID = '10009')

or union all if need also duplicated result

      SELECT CustID
          , DateSubmitted
          , Type
          , Points
              FROM `trans_summary`
                WHERE CustID = '10009'
    UNION ALL 

    SELECT CustID
    , DateSubmitted
    , Type
    , PointTotal 
        FROM `ptrans_detail` 
           WHERE CustID = '10009'
                and DateSubmitted NOT IN 
               (SELECT DateSubmitted FROM 
                 `trans_summary` 
                  WHERE CustID = '10009')
Comments