问题

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.

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

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

device_id | label    | pipe_division_id | ctrl_function
25          Ctrltab1     305              open_window 

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

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

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)

答案1

得分: 1

SELECT语句中的部分翻译如下：

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;

请注意，">"是HTML实体编码，表示大于号（>），在SQL查询中用于比较。

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;