Two tables: categories, and their many-to-many relations:
categories
id | name
1 first
2 second
3 third
relations
parent | child
1 2
1 3
How can I get this result?:
first | second
first | thi开发者_如何转开发rd
I only can
SELECT c.name, r.child
FROM categories AS c
LEFT JOIN relations AS r ON c.id = r.parent
And result is
first | 2
first | 3
So, how can I get child name in this table?
SELECT c1.name, c2.name
FROM relations AS r
LEFT JOIN categories AS c1 ON r.parent = c1.id
LEFT JOIN categories AS c2 ON r.child = c2.id
WHERE c1.id = 1
Select categories
table twice.
select parent_c.name parent, child_c.name child
from categories parent_c, categories child_c, relations r
where r.parent = parent_c.id and r.child = child_c.id
精彩评论