How to conditionally define the ON value of a JOIN in SQL?

I am facing an issue with a conditional join.

This is my query :

SELECT table_开发者_运维技巧b.name
FROM table_a
LEFT JOIN table_b
ON table_a.id = table_b.a_id

The description of my table_b :

id, a_id, ref_z, name
----------------------
1 | 0   |   1  | James
2 | 0   |   2  | John
3 | 2   |   2  | John. S
4 | 0   |   3  | Martin
5 | 6   |   3  | Martin. M
6 | 2   |   3  | Martin. M. Mr

Let's say I have a parameter @a_id.

I would like to join table_a and table_b ON table_a.id = @a_id if it exists else on 0.

So the output of the previous query will be (with @a_id = 2) :

James
John. S
Martin. M. Mr

Anyone has an idea, a blog post, a man page or anything else that can lead me to a correct query ?

Thank you,

where a_id = 2 

your join can be shorten into using (a_id) since both columns have the same name and there is no ambiguity.

on / using should not be confused for your predicate clause (where)

Assuming you have only one table, this worked for me

DECLARE @t TABLE
(
id int,
a_id int,
ref_z int,
name varchar(50)
)

INSERT INTO @t VALUES (1, 0, 1, 'James'),
(2, 0, 2, 'John'),
(3, 2, 2, 'John. S'),
(4, 0, 3, 'Martin'),
(5, 6, 3, 'Martin. M'),
(6, 2, 3, 'Martin. M. Mr')

DECLARE @a_id int = 2

SELECT COALESCE(table_b.name, (SELECT table_b2.name FROM @t AS table_b2 WHERE table_b2.a_id = 0 ORDER BY table_b2.id LIMIT 1)) AS name
FROM (
SELECT ref_z 
FROM @t AS table_z
GROUP BY ref_z
) AS table_z
LEFT OUTER JOIN @t AS table_b ON table_b.ref_z = table_z.ref_z AND table_b.a_id = @a_id

And the results:

James
John. S
Martin. M. Mr

Also note that this will also only display one record (the first) if there are multiple matches for a_id = 0

You could try using IFNULL with a sub select?:

SELECT ifnull(table_b.name,(select s.name from table_a sa inner join table_b sb on sa.a_id = sb.a_id where sb.a_id = 0 limit 1)) as name 
FROM table_a
LEFT JOIN table_b
ON table_a.id = table_b.a_id;

Untested I'm afraid but hope it helps!

Also - how do you want to handle multiple rows with a_id = 0? The above will only bring back the first row if there are multiples...