SQL Query

Peter B

[B]categories_users[/B]
cat_id
user_id

[B]categories[/B]
id  	
name
level1
level2
level3
level4

I need a list of all the categories where level3 = "18" and where applicable all the instances where the categories_users.user_id = 3038 where the tables join by categories.id = categories_users.cat_id. If there are no corresponding categories_users.cat_id where the user = 3038 the row should still appear but it should be null.

I think I have to do this with a left join only I don't know how to get the right answer.

SELECT * FROM categories c left join categories_users cu on c.id=cu.cat_id where c.level3="18" and cu.user_id = 3038

This does not give me all the rows I'm looking for.

I hope this makes sense.

FSL

It's not clear what you are trying to achieve.

Do you want a list of all entries in the Categories table where level3=18 and the Category id is in the Users Table with a user Id of 3038 plus a list of all category id's where the user_id=3038 and the Category Id does not have a level3=18. In which case you you just want a straight join where User id=3038 order by C.level3,C.id and the ones <>18 will appear in a block above and below the one where level3=18

Or do you want a list of all Categories with a level3 = 18 plus the Category Id and name of all categories where the User Id=3038 and the Level3 does not =18. In which case you can use a Union Clause select everything from Categories where level3=18 Union Select Id, Name, '' as level1.'' as level2.'' as level3 from a join of Categories and Users where User id=3038 and Level3<>18

OR do you want a list of all Categories where level3=18 and user id=3038 and all Category Id's in the User Table where User=3038 and the Cat id is not in the Categories table. In which case again a Union of your original query with a straight join Union Select Cat id as Id,'' as name,'' as level1,'' as level2,'' as level3 from Users where User Id=3038 and Cat id not in(Select distinct Id from Categories)

bothyhead

Is this what you're looking for by any chance?

SELECT
c.*
FROM
categories c
left join categories_users cu on (c.id=cu.cat_id and cu.user_id = 3038)
where
c.level3="18"