MySQL Query same column twice

Question

I have two tables one with ID and NAME

table 1

ID | NAME
1  | first
2  | second
3  | third

and an XREF table with ID and PARENT ID

table2

ID | PARENT ID
1  | 0
2  | 1
3  | 2

and I would like to retrieve the NAME twice like this: NAME | PARENT NAME

If it is possible to go three levels deep but with same 2-column table like this:

result table

NAME   | PARENT NAME
first  | NULL or EMPTY or this line the not showing at all
second | first
third  | second

... then I'd like to figure that out as well.

For the missing row you should take a look at outer joins (http://en.wikipedia.org/wiki/Join_(SQL)#Outer_joins). Regarding the "depth" I don't think I understood correctly what you need, so please try to add more details and examples if possible. — Morfic, Jun 14 '12 at 21:53
For 3 levels, just add another join. What you're trying to do is a mysql hierarchical queries http://stackoverflow.com/questions/8104187/mysql-hierarchical-queries in your case, you have multiple tables, but the concept is still similar. — xQbert, Jun 14 '12 at 21:58

score 1 · Accepted Answer · answered Jun 14 '12 at 21:53

1

select t1.Name, t12.Name from
table1 t1 
inner join table2 t2 on t1.ID = t2.ID 
inner join table1 t12 on t2.ParentID = t12.ID

This would only return 2 rows. If you want to have the first row (for ID=1) you just need to outer join instead.

answered Jun 14 '12 at 21:53

Klaus Byskov Pedersen

117,245
29
183
222

Thanks! I was thinking about this all day and could not solve by my selve. Thanks Again – Laci K Jun 14 '12 at 22:30

Zane Bien · Answer 2 · 2012-06-14T22:13:02.777

Consider putting the parentid in the first table as a self-referential relationship rather than having a separate table for it.

Ex.:

table1

ID  |  PARENTID  |  NAME
---------------------------
1      NULL         first
2      1            second
3      2            third

That way you would only need to join the table on itself rather than going through a 3rd table. (This is however assuming that the rows in table1 can only have a single parent, whereas your design allows one row to have multiple parents at a time)

But for your table structure, this will work:

SELECT
    a.name,
    c.name AS 'PARENT NAME'
FROM
    table1 a
LEFT JOIN
    table2 b ON a.id = b.id
LEFT JOIN
    table1 c ON b.parentid = c.id

But if you made the parentid in the same table referencing id, the SQL would be reduced to this:

SELECT
    a.name,
    b.name AS 'PARENT NAME'
FROM
    table1 a
LEFT JOIN
    table2 b ON a.parentid = b.id

I can't modify the tables because the db is connected to a partner's webservice which follows this structure and of course it wasn't made by me :) but thanks for your time and for your help! — Laci K, Jun 14 '12 at 23:08

MySQL Query same column twice

2 Answers2