Mysql join query

[Idee]

I have a table category and content

category table

Table
id parentid catname cattype contentid
1 0 index category 0
2 6 cat1 category 0
3 2 page1 content 1
4 1 page2 content 2
5 1 page3 content 3
6 0 delete category 0


A category can have content pages as child pages and if a category is deleted it will have parentid as 6 and children should also not be accessible.



Content
id name content
1 page1
2 page2
3 page3

I want to get all the content pages from objects whether they are child or parent and they should not be counted if either parent is deleted or it is deleted itself.

My query is like this but I am not sure if it will work in all situations

SELECT a.id,content
FROM category a
left join category b
on a.id = b.parentid and a.parentid <> 6
inner join content c
on c.id = a.contentid

Thanks

 

[snowboardr]

why dont you just do a nested loop?

 

[r937]

nested loop? gee, i hope you don't mean a query inside a loop

SELECT a.id
     , a.content
  FROM category a
inner 
  join content c
    on c.id = a.contentid
 where a.id <> 6
   and not exists
       ( select 1
           from category
          where id = a.parentid
            and parentid = 6 )

r937.com | rudy.ca