Search code examples
mysqlselectmulti-table

MYSQL selection - particular case

I have a first table T1 with 2 columns:

'id','info'

Examples of value:

'p1','infoP1'
'p2','infoP2'
'c1','infoC1'
'c2','infoC2'
'c3','infoC3'
'e4','infoE4'

I have a second table T2 with 2 column:

'id_p','id_c'

Examples of value:

'p1','c1'
'p1','c2'
'p2','c3'
'p2','c4'

I can't change the structure of these tables.
I want to make a SELECT to get the following result:

'idE','infoE','infoP'

with the following value:

'c1','infoC1','infoP1'
'c2','infoC2','infoP1'
'c3','infoC3','infoP2'
'c4','infoC4','infoP2'

But I haven't succeeded.
How can I achieve this?

EDIT: finally I did a mix of sub requests and I succeed to get the good result. Thank you all for helping me !

Solution

  • You should join T2 table two times with T1 table. First time to get "info" for "id_c" and second time to get "info" for "id_p".

    This should work:

    SELECT j.id_c, f.info, s.info 
FROM T1 as f, T2 as j, T1 as s 
WHERE f.id=j.id_c AND s.id=j.id_p;