SQL JOIN only in specific case

Question

I would like to - depending on the value of a column in table1 - either perform several joins or alternatively only display the contents of the main table1.

Unfortunately I don't get it. Here is my thought:

SELECT * FROM table1
CASE
 WHEN table1.more = 1
  THEN 
   LEFT JOIN table2 ON (table1.xyz= table2.xyz)
   LEFT JOIN table3 ON (table2.aaa= table3.aaa)
  ELSE
   END
WHERE
CASE
 WHEN table1.more = 1
  THEN table2.xyz = 12345
 ELSE
  table1.something = 1

Can someone help?

You need to study SQL a bit more. Play with it, read books and try online examples. This is just a case of not knowing enough about how the language fits together. @Parzival is probably correct, but until you understand the structure of the language his guidance may not be too helpful. — v0rl0n, Mar 25 '21 at 18:11

Parzival · Answer 1 · 2021-03-25T18:06:13.890

0

You probably want to look into the CASE keyword. Something like:

CASE WHEN table1.more = 1
THEN ...
ELSE ...
END

edited Mar 25 '21 at 18:06

answered Mar 25 '21 at 17:53

Parzival

2,051
4
14
32

Sorry, I forgot the CASE in my example. I corrected it accordingly. Would the syntax be allowed like that? – Tom Mar 25 '21 at 18:07
No - `CASE` cannot be used after the table name. You might want to consider joining the tables first, and then use the `CASE` to select the columns you needed – Parzival Mar 25 '21 at 18:39

score 0 · Answer 2 · answered Mar 25 '21 at 18:08

Please try this:

SELECT * FROM table1
LEFT JOIN table2 ON table1.xyz= table2.xyz and table1.more = 1
LEFT JOIN table3 ON table2.aaa= table3.aaa and table1.more = 1
WHERE
 (case WHEN table1.more = 1  THEN 12345 else table2.xyz end )=table2.xyz
 and
 (case WHEN table1.more = 1  THEN 1 else table1.something end )= 1

SQL JOIN only in specific case

2 Answers2