mysql nested query on same table

Hey all

So I have a generic content table, which is used for pages etc. some of these pages have parent / child relationships so I need to write a query that will grab info from both of them.

The ci_catid is a type of page which controls what is parent and child.

ci_link is a unique identifier so part of a URL can extract a specific page

$q = "SELECT *, (SELECT ci_id FROM content_items WHERE ci_link = '$link') AS parent_id FROM content_items WHERE ci_catid = '1' AND ci_category = '$type' AND ci_title = parent_id";

PHP:

Gets

Any ideas? Is nested queries a really bad way to do this? (i am rubbish with JOINs)

 

turns out aliases dont work in WHERE clauses, needed to use HAVING instead

 

I think I would try something like this:

$q = "
SELECT * FROM content_items
WHERE ci_title IN (SELECT ci_id FROM content_items WHERE ci_link = '$link')
AND ci_catid = '1' AND ci_category = '$type'";