CODEWITHSUNDEEP
mysqlsqldatabasesubstring
Fábio Rodrigues
Fábio Rodrigues

Reputation: 15

How to look for a value in SQL filtered in the same query

I will try to explain what i need through examples.

i have 2 tables

TableA
ID  Name
1   John
2   Mary

TableB
ID  IDNAME
1   1#ab
2   1#a
3   2#ac

So what i need is to select every id from TableB and get the name from tableA using the subtring_index function in tableB.IDNAME.

Example
SELECT `ID`, SUBSTRING_INDEX(`IDNAME`, '#', 1) AS 'NAME' FROM `tableB`

This will return the below result, but instead of the id value in the NAME column i want to get the correspond name from the tableA

TEMP Table
ID  NAME
1   1         <= John
2   1         <= John
3   2         <= Mary

Hope you can help me

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270473

You can do this as:

select b.*, a.name
from b left join
     a
     on substring_index(b.idname, '#', 1) = a.id;

You don't need substring_index(). An alternative method uses like:

select b.*, a.name
from b left join
     a
     on b.idname like concat(a.id, '#%');

Upvotes: 1

Related Questions