CODEWITHSUNDEEP
mysqlselectjoin
Button 108
Button 108

Reputation: 359

MySQL Select Combined Unique

Table: Contacts
id    | name  | has_this
------------------------
1     | Jeff  | 0
2     | Terry | 1
3     | Tom   | 0
4     | Henry | 1

Table: has_thing
id    | owner | thing
---------------------
1     | Terry | stuff
2     | Tom   | stuff
3     | Toby  | stuff

I want a SELECT that will return

name  | thing
-------------
Terry | stuff
Tom   | stuff
Henry | 
Toby  | stuff

Basically, I think I want a JOIN but I want any name that is in table 2(has_thing) that is not in table 1 to be included the output and any name that is in table 1(Contacts) WHERE has_this=1 to be included in the output

Upvotes: 0

Views: 37

Answers (1)

Barmar
Barmar

Reputation: 782130

SELECT name, MAX(thing) as thing
FROM (SELECT c.name, h.thing
      FROM Contacts AS c
      JOIN has_thing AS h ON c.name = h.name
      UNION
      SELECT name, ''
      FROM Contacts
      WHERE has_thing = 1) AS subquery
GROUP BY name

MAX(thing) ensures that we pick up the non-empty thing from the first query when the contact has has_thing = 1.

You could also do it with LEFT JOIN:

SELECT c.name, IFNULL(h.thing, '') AS thing
FROM Contacts AS c
LEFT JOIN has_thing AS h ON c.name = h.name
WHERE c.has_thing = 1
OR h.name IS NOT NULL

Upvotes: 1

Related Questions