How can I create select statement on this two tables

Question

How can I create sql statement on this, to output like this, I tried inner join but I could not get the name of p_id in usertable.

user_id         user_name       p_name                pid

1               millan          null                   

5               jane            millan                 1

6               lester          millan                 1

7               miller          jane                   5

8               jamie           jane                   5

here is my table. gather_table

p_id        u_id
null         1

1            5

1            6

5            7

5            8

user table

user_id      user_name

1            millan

2            john

3            max

4            chris

5            jane

6            lester

7            miller

8            jamie

Thank you in advance.

How do the id values in p_id and u_id relate? They seem to both link to the user table, but the exact way isn't clear. — Erik, Dec 09 '14 at 07:58

Abhik Chakraborty · Answer 1 · 2014-12-09T08:43:00.400

3

You need to join the user table two times to get the names

select
g.u_id,
u1.user_name,
u2.user_name as p_name,
g.p_id as pid
from gather_table g
left join user u1 on u1.user_id = g.u_id
left join user u2 on u2.user_id = g.p_id

edited Dec 09 '14 at 08:43

answered Dec 09 '14 at 08:03

Abhik Chakraborty

44,654
6
52
63

I get null for p_name – ashTon Dec 09 '14 at 08:42
sorry i had a typo i fixed it. – Abhik Chakraborty Dec 09 '14 at 08:43
it works now,I will just give you points I already accepted answers, it works too. – ashTon Dec 09 '14 at 09:02

score 1 · Accepted Answer · answered Dec 09 '14 at 08:03

1

Try this:

SELECT u1.user_id, u1.user_name, u2.user_name AS p_name, u2.user_id AS pid
FROM gather_table g 
LEFT JOIN user_table u1 ON g.u_id = u1.user_id 
LEFT JOIN user_table u2 ON g.p_id = u2.user_id

answered Dec 09 '14 at 08:03

Saharsh Shah

28,687
8
48
83

How can I create select statement on this two tables

2 Answers2