CODEWITHSUNDEEP
mysqlsqljoin
Boundless
Boundless

Reputation: 2464

How do I join two tables with the same composite key, and use values from T2 if not in T1 using MySQL

I have two tables with the same column names, and the same composite primary key. The composite primary key of both tables is id + year. I want to always use data from table T1, and if there exists an id + year from table T2, that's not in table T1 I want it to be returned also.

Here's my tables

T1

id  |  year  |  cost
----+--------+-------
1   |  2012  |  1.01
2   |  2012  |  1.02
2   |  2013  |  1.03

T2

id  |  year  |  cost
----+--------+-------
1   |  2012  |  2.01
2   |  2013  |  2.02
3   |  2013  |  2.03

I want to get:

id  |  year  |  cost
----+--------+-------
1   |  2012  |  1.01
2   |  2013  |  1.02
2   |  2013  |  1.03
3   |  2013  |  2.03

This is obviously wrong, but I tried:

SELECT
  T1.id,
  T1.year,
  T1.cost
FROM employee_cost AS T1
  INNER JOIN (select
        employee_cost_historic.id,
        employee_cost_historic.year,
        employee_cost_historic.cost
          from employee_cost_historic) AS T2
    ON T1.id = T2.id
      AND T1.year = T2.year

Upvotes: 1

Views: 5619

Answers (2)

sgeddes
sgeddes

Reputation: 62851

Or another option with LEFT JOIN and IS NULL:

SELECT T1.Id, T1.Year, T1.Code
FROM T1 
UNION
SELECT T2.Id, T2.Year, T2.Code
FROM T2
  LEFT JOIN T1 ON T1.Id = T2.Id AND T1.Year = T2.Year
WHERE T1.Id IS NULL

Good luck.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270583

You can do this with a union all:

select id, YEAR, cost
from t2
union all
select id, YEAR, cost
from t2
where not exists (select 1 from t1 where t1.id = t2.id and t1.year = t2.year)

Upvotes: 1

Related Questions