-1

Can you help me build a SQL query for getting list of data from 2 table where if data exist on second table, it should display data from it, otherwise from table1

Table 1:

ID | NAME    | AGE 
------------------
1  | John    | 20
2  | Daniel  | 30
3  | Abraham | 30
4  | Donald  | 25

Table 2:

ID | NAME    | AGE 
------------------
1  | John    | 23
2  | Donald  | 24

Desired result: John and Donald "AGE" get data from Table 2, and the rest from Table1

ID | NAME    | AGE 
------------------
1  | John    | 23
2  | Daniel  | 30
3  | Abraham | 30
4  | Donald  | 24

See http://www.sqlfiddle.com/#!9/9539b0

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
De Premor
  • 25
  • 5

2 Answers2

2

Tables joined using auto-increment IDs

SELECT t1.id, t1.name, IF(t2.id, t2.age, t1.age) AS age
FROM Clients AS t1
LEFT JOIN Records AS t2 ON (t1.id = t2.id)

Or tables joined using names

SELECT t1.id, t1.name, IF(t2.name, t2.age, t1.age) AS age
FROM Clients AS t1
LEFT JOIN Records AS t2 ON (t1.name = t2.name)
fubar
  • 16,918
  • 4
  • 37
  • 43
  • I've edited the first querry and it result as expected ` SELECT t1.id, t1.name, IF(t2.name=t1.name, t2.age, t1.age) AS age FROM Clients AS t1 LEFT JOIN Records AS t2 ON (t1.name = t2.name) ` – De Premor Apr 27 '17 at 01:18
  • Checking for equality in the `IF` isn't necessary. That's already handled by the `JOIN`. – fubar Apr 27 '17 at 07:25
  • `First query` give invalid result John 23, Daniel 24, Abraham 30 and Donald 25, and the `second query` give the result same as table 1, tested on this finddle [ http://www.sqlfiddle.com/#!9/9539b0/1 ] – De Premor Apr 27 '17 at 07:47
1

An outer join to Records, and test for a NULL value for age. Like this:

SELECT c.id
     , c.name
     , IFNULL(r.age,c.age) AS age
  FROM Clients c
  LEFT
  JOIN Records r
    ON r.name = c.name
 ORDER BY c.id

http://www.sqlfiddle.com/#!9/9539b0/4

spencer7593
  • 106,611
  • 15
  • 112
  • 140