MYSQL selection - particular case

Question

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 !

Welcome to SO. Where are your attempt(s)? So we can help you by explaining why something is wrong and why. — Sam Segers, Sep 14 '16 at 14:42
You columns you want to select `'idE','infoE','infoP'` what columns do they map to? — Henry, Sep 14 '16 at 22:47

score 1 · Answer 1 · answered Sep 14 '16 at 22:48

1

if you want to select data from two tables by using same id. The following query may help you:

SELECT * FROM TABLE1 AS T1 JOIN TABLE2 AS T2 WHERE T1.id = T2.id

answered Sep 14 '16 at 22:48

vinay kumar reddy

Thank you, but my question was not about join table, but succeed to get in the result 2 columns on the same table, with different value. – Kozame Sep 15 '16 at 09:18

score 0 · Accepted Answer · answered Sep 14 '16 at 23:19

0

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;

answered Sep 14 '16 at 23:19

Chart

Thank you for you proposition. It's very interresting. I get the good count of row, but the data of the second column is the same of the third column. So it's not exactly what I want – Kozame Sep 15 '16 at 09:12
Have you certain about that? Because I tried this and works fine here... See image bellow: http://imgur.com/a/4ByIQ – Chart Sep 15 '16 at 13:13
Maybe you need to change the alias for output columns. Look: [SELECT j.id_c, f.info as info_c, s.info as info_p ...] – Chart Sep 15 '16 at 13:15
That really help me, but I can't upvote your answer, I don't have enough reputation :/ Indeed, I think I made something wrong : my request is more complex than the example I did, so I had to do something wrong. – Kozame Sep 16 '16 at 09:41
I test it with a simple example and that works, so the problem is on my side, your proposition is perfect ! – Kozame Sep 16 '16 at 10:01

2 Answers2