CODEWITHSUNDEEP
mysqlsqljoinleft-join
workwise
workwise

Reputation: 1103

MySQL left join variation - selecting even on missing rows

I could already figure out how to join and select rows from a primary table, even if secondary table does not have matching rows. What I want is a little different.

Table A:  id, pid
Table B:  id, pid, uid, aid

What I want is all pids in table A, with matching aid, if it exists, for a given uid

That is, if example data is:

Table A:

1,  p1
2,  p2
3,  p3

Table B:

1, p1,  u1,   abcd
2, p1,  u2,   pqrs
3, p2,  u1,   wxyz

I want to select:

p1,   abcd
p2,   wxyz
p3,   NULL

(all entries for u1, use aid=NULL if there is no entry).

So basically, I have tried:

SELECT A.* FROM A LEFT JOIN B ON A.pid=B.pid WHERE uid='u1'

and it does not work (I understand why it wont).

Upvotes: 1

Views: 991

Answers (2)

Abhishek Gupta
Abhishek Gupta

Reputation: 4166

Below query should solve your issue

SELECT A.pid, aid
FROM A LEFT JOIN B
ON A.pid = B.pid and B.uid = "u1";

OUTPUT http://sqlfiddle.com/#!2/5eaa05/10

Your query

SELECT A.* 
FROM A LEFT JOIN B 
ON A.pid=B.pid 
WHERE uid='u1'

OUTPUT http://sqlfiddle.com/#!2/5eaa05/12

The issue is with WHERE, LEFT JOIN works as an INNER JOIN.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can do this with a left join, but you need to move the condition on the b.uid to the on clause from the where clause:

select a.id, a.pid, b.aid
from a left join
     b
     on a.pid = b.pid and b.uid = 1;

Upvotes: 4

Related Questions