Ask a SQL about “ select … join … ”

There are two tables.

Table A :

id	name
1	nameX
2	nameY

Table B :

id	aId	name
1	2	Foo
2	2	Bar

Ask a SQL:

when search ‘nameY’, get

A.id	A.name	B.id	B.aId	B.name
2	nameY

when search ‘Bar’, get

A.id	A.name	B.id	B.aId	B.name
2	nameY	2	2	Bar

–

SQL:

select * from A 
    left join B on A.id = B.aId 
    where A.name like '%nameY%' or B.name like '%nameY%';

But get two rows. I don’t need the table B when search ‘nameY’.

A.id	A.name	B.id	B.aId	B.name
2	nameY	1	2	Foo
2	nameY	2	2	Bar

Answer

You should join B table only when B table has searched name.

Add “name condition” to LEFT JOIN of B table

SELECT A.id, A.name, B.id, B.name
FROM A
    LEFT JOIN B ON B.aid = A.id AND B.name LIKE '%search%'
WHERE A.name LIKE '%search%' OR B.name LIKE '%search%'