select categories with parent name from one table

Question

Hello,

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 creating 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

Answer 1

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 

Answer 2

Hi,

you're looking for an OUTER JOIN :)

See here: http://www.quackit.com/sql/tutorial/sql_outer_join.cfm

Answer 3

To help wrap your mind around SQL Joins, check out Jeff Atwood's Visual Guide