CODEWITHSUNDEEP
mysqljoin
Mitch Lillie
Mitch Lillie

Reputation: 2407

Join one table to two separate one-to-many tables

I have three tables, like so:

projects
| id | name       |
|----|------------|
| 1  | enterprise |
| 2  | discovery  |

widgets
| project_id | name            |
|------------|-----------------|
| 1          | saucer section  |
| 1          | pylons          |
| 2          | spinning saucer |
| 2          | angular pylons  |

sprockets
| project_id | name        |
|------------|-------------|
| 1          | engineering |
| 1          | bridge      |
| 1          | mess        |
| 2          | engineering |
| 2          | bridge      |
| 2          | mess        |

What I am trying to write is a query that gives me exactly ten results: essentially one for each row in widgets and sprockets, that looks like this:

result
| project_name | widget_name     | sprocket_name |
|--------------|-----------------|---------------|
| enterprise   | saucer section  | null          |
| enterprise   | pylons          | null          |
| enterprise   | null            | engineering   |
| enterprise   | null            | bridge        |
| enterprise   | null            | mess          |
| discovery    | spinning saucer | null          |
| discovery    | angular pylons  | null          |
| discovery    | null            | engineering   |
| discovery    | null            | bridge        |
| discovery    | null            | mess          |

Instead, my joins are combining to return 12 rows. Adding a group by seems to reduce it too much.

I've tried something like the following, but the joins are multiplied:

select
  p.name as project_name,
  w.name as widget_name,
  s.name as sprocket_name
from
  projects as p
left join widgets w on p.id = w.project_id
left join sprockets s on p.id = s.project_id;

The answers I've found mostly focus on redesigning the database, however that is not an option for me. How can I write a join to return the above ten rows from this dataset?

Upvotes: 0

Views: 43

Answers (1)

Barmar
Barmar

Reputation: 781310

Use a UNION of joins with each table.

SELECT p.name AS project_name, w.name AS widget_name, NULL AS sprocket_name
FROM projects AS p
INNER JOIN widgets AS w ON p.id = w.project_id

UNION ALL

SELECT p.name AS project_name, NULL AS widget_name, s.name AS sprocket_name
FROM projects AS p
INNER JOIN sprockets AS s ON p.id = s.project_id

Upvotes: 3

Related Questions