Issue
Given I have two SQL tables:
movies
----------------
| id | title |
----------------
| 1 | Matrix |
| 2 | Titanic |
----------------
directors
-----------------------
| id | name | movieid |
-----------------------
| 1 | Steve | 1 |
-----------------------
If I do a typical left join;
SELECT *
FROM movies
LEFT JOIN directors ON directors.movieid = movies.id
Then then output will be similar to:
-----------------------------------------
| id | title | id | name | movieid |
-----------------------------------------
| 1 | Matrix | 1 | Steve | 1 |
| 2 | Titanic | NULL | NULL | NULL |
-----------------------------------------
Because for movie 2
there is no director
, as expected.
However, in the cases where the join results in a NULL
match, I want to provide a default value:
In pseudo SQL:
SELECT *
FROM movies
LEFT JOIN directors ON directors.movieid = (movies.id || 1)
Meaning, if the join results in a NULL
match, join on the (default) value 1
instead, which would return ideally for me:
-----------------------------------------
| id | title | id | name | movieid |
-----------------------------------------
| 1 | Matrix | 1 | Steve | 1 |
| 2 | Titanic | 1 | Steve | 1 |
-----------------------------------------
Meaning, I want to provide a default director for all movies, if the JOIN fails.
Is it possible to do something like this in (Postgres) SQL?
Solution
Since you need to work with additional joins and operations, you need to create a CTE for directors of each movie and then use that CTE table instead of the directors table.
WITH cte
AS (
SELECT COALESCE(directors.id, d2.id) AS id
,COALESCE(directors.name, d2.name) AS name
,COALESCE(movies.id, d2.movieid) AS movieid
FROM movies
LEFT JOIN directors ON directors.movieid = movies.id
LEFT JOIN directors d2 ON d2.movieid = 1
)
SELECT *
FROM movies
LEFT JOIN cte ON cte.movieid = movies.id
Output with additional data:
id | title | id | name | movieid |
---|---|---|---|---|
1 | Matrix | 1 | Steve | 1 |
2 | Titanic | 1 | Steve | 2 |
3 | Avatar | 2 | Mark | 3 |
4 | Mission Impossible | 1 | Steve | 4 |
See this fiddle.
In case in movieid
field you require it to match with directors' table, add another field in cte ,COALESCE(directors.movieid, d2.movieid) AS dmovieid
and use that in SELECT
statement (but don't use it in the join). E.g. here
Answered By - Himanshu Answer Checked By - Gilberto Lyons (PHPFixing Admin)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.