CODEWITHSUNDEEP
mysqlsql
Easyrider
Easyrider

Reputation: 3269

Merge data from 2 tables, use only unique rows

I have 2 tables in my database

primary_id
primary_date
primary_measuredData 

temporary_id
temporary_date
temporary_measuredData

well. the table have other columns but these are the important ones.

What I want is the following.

Table "primary" consists of verified measuredData.If data is available here, the output should choose first from primary, and if not available in primary, choose from temporary.

In about 99.99% of the cases all old data is in the primary, and only the last day is from the temporary table.

Example:

primary table:
2013-02-05; 345
2013-02-07; 123
2013-02-08; 3425
2013-02-09; 334

temporary table:
2013-02-06; 567
2013-02-07; 1345
2013-02-10; 31
2013-02-12; 33

I am looking for the SQL query that outputs:

2013-02-05; 345   (from primary)
2013-02-06; 567   (from temporary, no value available from prim)
2013-02-07; 123   (from primary, both prim & temp have this date so primary is used)
2013-02-08; 3425  (primary)
2013-02-09; 334   (primary)
2013-02-10; 31    (temp)
2013-02-12; 33    (temp)

you see, no duplicate dates and if data is avalable at primary table then the data is used from that one.

I have no idea how to solve this, so I cant give you any "this is what I've done so far :D"

Thanks!

EDIT: The value of "measuredData" can differ from temp and primary. This is because temp is used to store a temporary value, and later when the data is verified it goes into the primary table.

EDIT 2: I changed the primary table and added a new column "temporary". So that I store all the data in the same table. When the primary data is updated it updates the temporary data with the new numbers. This way I dont need to merge 2 tables into one.

Upvotes: 2

Views: 604

Answers (1)

fthiella
fthiella

Reputation: 49089

You should start with a UNION QUERY like this:

SELECT p.primary_date AS dt, p.primary_measuredData as measured
FROM
  `primary` p
UNION ALL
SELECT t.temporary_date, t.temporary_measuredData
FROM
  `temporary` t  LEFT JOIN `primary` p
  ON p.primary_date=t.temporary_date
WHERE p.primary_date IS NULL

a LEFT JOIN where there's no match (p.primary_date IS NULL) will return all rows from the temporary table that are not present in the primary table. And using UNION ALL you can return all rows available in the first table.

You might want to add an ORDER BY clause to the whole query. Please see fiddle here.

Upvotes: 1

Related Questions