Optional JOIN in SQL

huangapple
go评论94阅读模式
英文:

Optional JOIN in SQL

问题

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

  1. device_id | name   | board_number 
  2. --------------------------------
  3. 23         Stop1    10
  4. 24         Stop2    11
  5. 25         Ctrltab1 11
  6. 26         Rand_dev 8

Stop table

  1. device_id | label        | pipe_division_id | length
  2. 23          Stop1: Piano   305                16
  3. 24          Stop2: Buffet  306                16

Ctrltab table

  1. device_id | label    | pipe_division_id | ctrl_function
  2. 25          Ctrltab1     305              open_window

Pipe Division table

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

Expected result of query:

  1. name   | board_number | label
  2. Stop1    10             Lower Box
  3. Stop2    11             Upper Box
  4. 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

  1. Select name, board_number, pd.label 
  2. from device d 
  3. JOIN stops s ON s.device_id = d.device_id 
  4. JOIN ctrltab ct ON ct.device_id = d.device_id 
  5. JOIN pipe_division_id pd ON (s.pipe_division_id = pd.pipe_division_id 
  6.                          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.

英文:

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

  1. device_id | name   | board_number 
  2. --------------------------------
  3. 23         Stop1    10
  4. 24         Stop2    11
  5. 25         Ctrltab1 11
  6. 26         Rand_dev 8

Stop table

  1. device_id | label        | pipe_division_id | length
  2. 23          Stop1: Piano   305                16
  3. 24          Stop2: Buffet  306                16

Ctrltab table

  1. device_id | label    | pipe_division_id | ctrl_function
  2. 25          Ctrltab1     305              open_window

Pipe Division table

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

Expected result of query:

  1. name   | board_number | label
  2. Stop1    10             Lower Box
  3. Stop2    11             Upper Box
  4. 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

  1. Select name, board_number, pd.label 
  2. from device d 
  3. JOIN stops s ON s.device_id = d.device_id 
  4. JOIN ctrltab ct ON ct.device_id = d.device_id 
  5. JOIN pipe_division_id pd ON (s.pipe_division_id = pd.pipe_division_id 
  6.                          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.

答案1

得分: 1

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

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

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

英文: 
  1. SELECT d.name, d.board_number, COALESCE(pd1.label, pd2.label) AS label
  2. FROM device d
  3. LEFT JOIN stop s ON s.device_id = d.device_id
  4. LEFT JOIN pipe_division pd1 ON s.pipe_division_id = pd1.pipe_division_id
  5. LEFT JOIN ctrltab ct ON ct.device_id = d.device_id
  6. LEFT JOIN pipe_division pd2 ON ct.pipe_division_id = pd2.pipe_division_id
  7. WHERE d.board_number > 10;

Dbfiddle

huangapple
  • 本文由 发表于 2023年2月14日 02:45:55
  • 转载请务必保留本文链接：https://go.coder-hub.com/75440041.html
  • postgresql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定