CODEWITHSUNDEEP
sqlpostgresql
nomad culture
nomad culture

Reputation: 52

full outer join or right outer join

I have two tables as shown:

cat_id | cat_name
1      | free
2      | semi-paid
3      | premium

payment_id | user_id | cat_id
1          | 1       | 1
2          | 1       | 4

I want to query result like below

payment_id | user_id | cat_name
1          | 1       | free
null       | 1       | semi-paid
null       | 1       | premium
2          | 1       | null

I will show no payment icon in UI-on profile page- if payment_id is null. It means user_id=1 haven't used anything from semi-paid and premium categories.

I tried 

SELECT payment_id, user_id, cat_name 
FROM payment 
FULL JOIN category 
  ON payment.cat_id 
WHERE user_id = 1

But I got a cartesian table :( Could you please help me?

Upvotes: 0

Views: 55

Answers (3)

Aaron Dietz
Aaron Dietz

Reputation: 10277

You will need to put your user_id = 1 into your join condition or you will filter out the NULL rows. Then all you need is NULL handling on user_id:

SELECT payment_id, COALESCE(user_id,1) as user_id, cat_name 
FROM payment p
FULL JOIN category c ON p.cat_id = c.cat_id 
                    AND p.user_id = 1

You could also do:

SELECT payment_id, 1 as user_id, cat_name 
FROM payment p
FULL JOIN category c ON p.cat_id = c.cat_id 
                    AND p.user_id = 1

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SQL DEMO

SELECT c.*, p.*, payment_id, user_id, cat_name
FROM category c
LEFT JOIN payment p
  ON c.cat_id = p.cat_id
 AND p.user_id = 1
UNION
SELECT c.*, p.*, payment_id, user_id, cat_name
FROM category c
RIGHT JOIN payment p
  ON c.cat_id = p.cat_id
 AND p.user_id = 1

OUTPUT focus on the last 3 columns

| cat_id |  cat_name | payment_id | user_id | cat_id | payment_id | user_id |  cat_name |
|--------|-----------|------------|---------|--------|------------|---------|-----------|
| (null) |    (null) |          2 |       1 |      4 |          2 |       1 |    (null) |
|      1 |      free |          1 |       1 |      1 |          1 |       1 |      free |
|      2 | semi-paid |     (null) |  (null) | (null) |     (null) |  (null) | semi-paid |
|      3 |   premium |     (null) |  (null) | (null) |     (null) |  (null) |   premium |

Upvotes: 1

Nikhil Shetkar
Nikhil Shetkar

Reputation: 346

Try this :

 SELECT payment_id,
    case when payment_id is null 
         then 1 
         else user_id end user_Id, 
     cat_name 
    FROM payment 
    FULL outer JOIN category 
      ON payment.cat_id = category.cat_id;

Upvotes: 0

Related Questions