CODEWITHSUNDEEP
mysqlsql
Ken Y-N
Ken Y-N

Reputation: 15008

How to write a JOIN when the second table has an ON match but not a WHERE match

The title sounds a bit odd, but what I am trying to do is as follows. Given the tables

A.a  A.b         B.a  B.c  B.d
---  ---         ---  ---  ---
1    X           1    10   Z
2    Y           1    30   W

I want a query that might look sort of like the following:

SELECT A.a, B.d FROM A
some kind of JOIN B ON A.a = B.a
...WHERE A.a = :a AND B.c = :c

Now, if I do the following replacements I want to get these answers:

:a  :c       A.a  B.d
--  --       ---  ---
1   10  -->  1    Z
1   20  -->  1    NULL
2   any -->  empty set

I'm using MySQL so FULL JOIN is out of the question.

NOT SOLVED

I thought I had worked it out myself:

SELECT A.a, CASE WHEN B.c = :c THEN B.d ELSE NULL FROM A 
LEFT JOIN B ON A.a = B.a
WHERE A.a = :a AND B.c = :c

But that gives:

:a  :c       A.a  B.d
--  --       ---  ---
1   20  -->  1    NULL
             1    10

Upvotes: 2

Views: 78

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43484

Try this:

select distinct a1.a, b2.d from A a1
join B b1 on a1.a = b1.a
left join B b2 on a1.a = b2.a and b2.c = 10
where a1.a = 1

Upvotes: 3

Related Questions