CODEWITHSUNDEEP
sqlpostgresqljoinwhere-clause
Hesham Mahdi
Hesham Mahdi

Reputation: 33

How to select from joined table with WHERE clause only when value exist?

I want to SELECT one record from table1 (WHERE t1.id = 1) and then JOIN table2 and table3 (t2.field2 and t3.field3) to table1 but ONLY if the values exists (IS NOT NULL).

So for example, if the value doesn't exist for t3.field3, the field3 column is not displayed for that table...

   t1

 id  |  field1
---------------
 1   |   f1val
 2   |   f1val
 3   |   f1val


       t2

 id(fk)  |  field2
-------------------
 1       |   f2val
 2       |   null
 3       |   null


       t3

 id(fk)  |  field3
-------------------
 1       |   null
 2       |   f3val
 3       |   f3val

the code I tried to do is this:

    SELECT t1.id, t2.field1, t3.field3
    FROM (
       SELECT t1.id
       FROM t1
       WHERE t1.id = 1
    )
   LEFT JOIN t2 ON t2.id = t1.id AND t2.id is not null
   LEFT JOIN t3 ON t2.id = t1.id AND t3.id is not null;

The joined table returned from the query above looks like this:

 id  |  field2  | field3
----------------------------
  1  |   f1val  |  null

However, since field3 is null, I want it to return only the id and field2 like this:

  id  |  field2  
----------------
   1  |  f1val

Your help will be highly appreciated.

Upvotes: 1

Views: 1107

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

You could return one column, using coalesce():

SELECT t1.id, COALESCE(t2.field1, t3.field3) as field_2_3
FROM t1 LEFT JOIN
     t2 
     ON t2.id = t1.id LEFT JOIN
     t3
     ON t3.id = t1.id
WHERE t1.id = 1;

However, you cannot sometimes return two columns and sometimes return 3 columns.

Notes:

  • The subquery on t1 is utterly unnecessary. You can just apply the filter in a single WHERE clause.
  • The comparisons for IS NOT NULL are unnecessary because they fail the JOIN condition anyway.
  • The last JOIN condition is presumably on t3.id = t1.id.

Upvotes: 1

Related Questions