CODEWITHSUNDEEP
mysqlsqljoinnull
P00LR0CK
P00LR0CK

Reputation: 29

SQL query to JOIN two tables returning null when not found

table: p
p_id/t_id/
1,35
2,35
3,35
4,35
5,35
6,22
7,22
...

for the table p, all the possible value for p_id has been registered

table: mps
p_id/m_id/value
2,1001,0
4,1001,2
7,1005,1
...

for the table: mps, just a few values of p_id has been registered.

It has to display all the p_id combinations and when it does not find data in the table mps, just display null.

output example:
p_id, m_id, value
1,1001,null
2,1001,0
3,1001,null
4,1001,2
5,1001,null

I have tried with different SQL queries, but I am not getting the output when p_id is not registered on the table mps:

SELECT p.p_id, mps.value, mps.m_id 
FROM p LEFT JOIN
     mps
     ON p.p_id = mps.p_id 
WHERE p.t_id = 35 AND mps.m_id=1001

Upvotes: 0

Views: 2208

Answers (3)

Job Curtis
Job Curtis

Reputation: 175

What you're looking for in mps.m_id is data that sometimes doesn't exist, so you have to provide it yourself;

SELECT p.p_id
     , 1001 AS m_id
     , mps.value
FROM p
   LEFT JOIN mps ON p.p_id = mps.p_id AND mps.m_id = 1001
WHERE p.p_id = 35;

Upvotes: 0

Nick
Nick

Reputation: 147206

Since you will get NULL mps values for any p_id which is not in mps, you need to COALESCE the value of m_id to make it equal to 1001 for the values of p_id not in mps:

SELECT p.p_id, COALESCE(mps.m_id, 1001) AS m_id, mps.value
FROM p 
LEFT JOIN mps ON p.p_id = mps.p_id AND mps.m_id = 1001
WHERE p.t_id = 35
ORDER BY p.p_id

Output:

p_id    m_id    value
1       1001    (null)
2       1001    0
3       1001    (null)
4       1001    2
5       1001    (null)

Demo on SQLFiddle

Alternatively (so as not to repeat the 1001 value) you can CROSS JOIN to a derived table consisting of an m_id = 1001 and then LEFT JOIN to mps:

SELECT p.p_id, m.m_id AS m_id, mps.value
FROM p 
CROSS JOIN (SELECT 1001 AS m_id) m
LEFT JOIN mps ON p.p_id = mps.p_id AND mps.m_id = m.m_id
WHERE p.t_id = 35
ORDER BY p.p_id

Output is the same as the previous query. Demo on SQLFiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I think you just want to move the filtering t_id to a WHERE clause:

SELECT p.p_id, mps.value, mps.m_id, 
FROM p LEFT JOIN
     mps
     ON mps.p_id = p.p_id and mps.m_id = 1001
WHERE p.t_id = 35

Upvotes: 1

Related Questions