Getting NULL values in JOINED table with LIMIT

Question

There are many similar questions which I've learned from, but my result set isn't returning the expected results.

My Objective:

Build a query that will return a result set containing all rows in table demo1 with user_id = "admin", and the only row of table demo2 with user_id = "admin". Each row in demo2 has a unique user_id so there's always only one row with "admin" as user_id.

However, I don't want demo2 data to wastefully repeat on every subsequent row of demo1. I only want the first row of the result set to contain demo2 data as non-null values. Null values for demo2 columns should only be returned for rows 2+ in the result set.

Current Status:

Right now my query is returning the appropriate columns (all demo1 and all demo2) but all the data returned from demo2 is null.

Demo1:

id  user_id  product  quantity  warehouse
1   admin    phone    3         A
2   admin    desk     1         D 
3   k45      chair    5         B

Demo2:

id  user_id  employee  job   country
1   admin    james     tech  usa
2   c39      cindy     tech  spain

Query:

SELECT * 
from  demo1
left join  (SELECT * FROM demo2 WHERE demo2.user_id = 'X' LIMIT 1) X
on (demo1.user_id = x.user_id)
WHERE demo1.user_id = 'admin'

Rationale:

The subquery's LIMIT 1 was my attempt to retrieve demo2 values for row 1 only, thinking the rest would be null. Instead, all values are null.

Current Result:

id  user_id  product quantity warehouse id    employee  job   country
1   admin    phone   3        A         null  null      null  null
2   admin    desk    1        D         null  null      null  null

Desired Result:

id  user_id  product quantity warehouse id    employee  job   country
1   admin    phone   3        A         1     james     tech  usa
2   admin    desk    1        D         null  null      null  null

I've tried substituting left join for left inner join, right join, full join, but nothing returns the desired result.

Answer 1

Your join is going to bring through ANY records that satisfies the join condition for your two tables. There is no changing that.

But you could suppress subsequent records in your result set from displaying the matching demo2 record that satisfied the join condition AFTER it's joined:

SELECT demo1.id ,
    demo1.user_id,
    demo1.product,
    demo1.quantity,
    demo1.warehouse 
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY demo1.user_id ORDER BY demo1.id) = 1 THEN demo2.id END as demo2_id,    
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY demo1.user_id ORDER BY demo1.id) = 1 THEN demo2.employee END AS demo2_employee,  
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY demo1.user_id ORDER BY demo1.id) = 1 THEN demo2.job END as demo2_job,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY demo1.user_id ORDER BY demo1.id) = 1 THEN demo2.country END as demo2_country
from  demo1
left join  demo2 
    on demo1.user_id = demo2.user_id
        AND demo2.user_id = 'X'
WHERE demo1.user_id = 'admin'

That's just a quick rewrite of your original sql with the addition CASE expressions included.

That being said, this sql will produce no results for demo2 since the demo2.user_id can't satisfy both conditions in this query:

1. The join condition demo1.user_id = demo2.user_id with the where predicate of demo1.user_id = 'admin'

2. Also hold the value X.

It's either admin and satisfies your first join condition, but fails your second. Or it's X and satisfies your second condition, but nor your first.

Answer 2

Here is another nice approach: sqlfiddle