CODEWITHSUNDEEP
sqlleft-join
Eric
Eric

Reputation: 97565

SQL left join two tables independently

If I have these tables:

Thing
id | name
---+---------
1  | thing 1
2  | thing 2
3  | thing 3

Photos
id | thing_id | src
---+----------+---------
1  | 1        | thing-i1.jpg
2  | 1        | thing-i2.jpg
3  | 2        | thing2.jpg

Ratings
id | thing_id | rating
---+----------+---------
1  | 1        | 6
2  | 2        | 3
3  | 2        | 4

How can I join them to produce

id | name    | rating | photo
---+---------+--------+--------
1  | thing 1 | 6      | NULL
1  | thing 1 | NULL   | thing-i1.jpg
1  | thing 1 | NULL   | thing-i2.jpg

2  | thing 2 | 3      | NULL
2  | thing 2 | 4      | NULL
2  | thing 2 | NULL   | thing2.jpg

3  | thing 3 | NULL   | NULL

Ie, left join on each table simultaneously, rather than left joining on one than the next?

This is the closest I can get:

SELECT Thing.*, Rating.rating, Photo.src
From Thing
Left Join Photo on Thing.id = Photo.thing_id
Left Join Rating on Thing.id = Rating.thing_id

Upvotes: 2

Views: 1834

Answers (3)

Eric
Eric

Reputation: 97565

Here's what I came up with:

SELECT
  Thing.*,
  rp.src,
  rp.rating
FROM
  Thing
  LEFT JOIN (
    (
      SELECT
        Photo.src,
        Photo.thing_id AS ptid,
        Rating.rating,
        Rating.thing_id AS rtid
      FROM
        Photo
        LEFT JOIN Rating
          ON 1 = 0
    )
    UNION
    (
      SELECT
        Photo.src,
        Photo.thing_id AS ptid,
        Rating.rating,
        Rating.thing_id AS rtid
      FROM
        Rating
        LEFT JOIN Photo
          ON 1 = 0
    )
  ) AS rp
    ON Thing.id IN (rp.rtid, rp.ptid)

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116100

You can get the results you want with a union, which seems the most obvious, since you return a field from either ranking or photo.

Your additional case (have none of either), is solved by making the joins left join instead of inner joins. You will get a duplicate record with NULL, NULL in ranking, photo. You can filter this out by moving the lot to a subquery and do select distinct on the main query, but the more obvious solution is to replace union all by union, which also filters out duplicates. Easier and more readable.

select
  t.id,
  t.name,
  r.rating,
  null as photo
from
  Thing t
  left join Rating r on r.thing_id = t.id
union
select
  t.id,
  t.name,
  null,
  p.src
from
  Thing t
  left join Photo p on p.thing_id = t.id
order by
  id,
  photo,
  rating

Upvotes: 2

Wolph
Wolph

Reputation: 80011

MySQL has no support for full outer joins so you have to hack around it using a UNION:

Here's the fiddle: http://sqlfiddle.com/#!2/d3d2f/13

SELECT *
FROM (
    SELECT Thing.*,
           Rating.rating,
           NULL AS photo
    FROM Thing
    LEFT JOIN Rating ON Thing.id = Rating.thing_id

    UNION ALL

    SELECT Thing.*,
           NULL,
           Photo.src
    FROM Thing
    LEFT JOIN Photo ON Thing.id = Photo.thing_id
) s
ORDER BY id, photo, rating

Upvotes: 0

Related Questions