Optional JOIN in SQL

Question

I have a parent device table, as well as the division table. In between there are the stops and ctrltab tables, each of which have properties device_id that matches with device, as as pipe_division_id which connects to the pipe_division table. EDIT: By request I have added sample tables below

Device table

device_id | name   | board_number 
--------------------------------
23         Stop1    10
24         Stop2    11
25         Ctrltab1 11
26         Rand_dev 8

Stop table

device_id | label        | pipe_division_id | length
23          Stop1: Piano   305                16
24          Stop2: Buffet  306                16

Ctrltab table

device_id | label    | pipe_division_id | ctrl_function
25          Ctrltab1     305              open_window

Pipe Division table

pipe_division_id | label     | position
305                Lower Box    underneath the stairs
306                Upper Box    above the stairs
307                Side Box     To the left of the console in the closet

Expected result of query:

name   | board_number | label
Stop1    10             Lower Box
Stop2    11             Upper Box
Ctrltab1 11             Lower Box

Essentially, I would like to select all the devices in the device table with a board number higher than 10, that have a pipe_division_id that is found by connecting their stop or ctrltab tables.

I tried

Select name, board_number, pd.label 
from device d 
JOIN stops s ON s.device_id = d.device_id 
JOIN ctrltab ct ON ct.device_id = d.device_id 
JOIN pipe_division_id pd ON (s.pipe_division_id = pd.pipe_division_id 
                         OR ct.pipe_division_id = pd.pipe_division_id)

but this does not bring up any results, I suppose this OR statement is not valid? I would rather avoid a union as the real query is very long and I wouldn't like to repeat all that text just for a minor difference in finding the pipe_division label.

A few rows of sample data and the expected result would help us greatly to understand what you need. — The Impaler, Feb 13 '23 at 18:48

Bill Karwin · Accepted Answer · 2023-02-13T19:08:46.623

1

SELECT d.name, d.board_number, COALESCE(pd1.label, pd2.label) AS label
FROM device d
LEFT JOIN stop s ON s.device_id = d.device_id
LEFT JOIN pipe_division pd1 ON s.pipe_division_id = pd1.pipe_division_id
LEFT JOIN ctrltab ct ON ct.device_id = d.device_id
LEFT JOIN pipe_division pd2 ON ct.pipe_division_id = pd2.pipe_division_id
WHERE d.board_number > 10;

Dbfiddle

edited Feb 13 '23 at 19:08

answered Feb 13 '23 at 19:02

Bill Karwin

538,548
86
673
828

2

No explanation needed, because it's here: [What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) – Luuk Feb 13 '23 at 19:08

Optional JOIN in SQL

1 Answers1