select categories with parent name from one table
I got an existing products database which I'm writing an administration tool for (in PHP). The database contains the following "categories" table:
Table Categories
--------------------
PK | id
FK | parent_id
| title
Now the foreign key "parent_id" contains an id taken from the same table, or "0" if it's a topmost category.
For creatin开发者_C百科g an overview I now need a mysql statement which results in the following data:
id | parent_id | title | parent_title
The parent_title is where I've no idea. I created the following statement:
SELECT
c1.id,
c1.parent_id,
c1.title,
c2.title as `parent_title`
FROM
categories c1,
categories c2
WHERE
c1.parent_id = c2.id
I now only get all categories which have got a parent category.
Should be simple, and might have already been answered here. I think I only didn't find the right words to search for to find it by searching existing articles.
Thanks for your help, Daniel
You can use a LEFT OUTER JOIN
for this:
SELECT c1.id,
c1.parent_id,
c1.title,
c2.title as `parent_title`
FROM categories c1
left outer join categories c2 on c1.parent_id = c2.id
you're looking for an OUTER JOIN :)
See here: http://www.quackit.com/sql/tutorial/sql_outer_join.cfm
精彩评论