SQL Join Problem

[francisnrb]

Hi I have an SQL problem with this query:

SELECT cat_id, parent_id FROM categories Categories LEFT OUTER JOIN user_categories UserCategories ON Categories.cat_id = UserCategories.cat_id LEFT OUTER JOIN role_categories RoleCategories ON Categories.cat_id = RoleCategories.cat_id WHERE UserCategories.user_id = 11111 AND RoleCategories.role_id = 91919

How do i get the results that are not on the UserCategories table but are on the RoleCategories table?

Categories Table Data:
cat_id = 1, parent_id = 0
cat_id = 2, parent_id = 1
cat_id = 3, parent_id = 2
cat_id = 4, parent_id = 3
cat_id = 5, parent_id = 4
cat_id = 6, parent_id = 5

RoleCategories Table Data:
role_id = 91919, cat_id = 1
role_id = 91919, cat_id = 2
role_id = 91919, cat_id = 3
role_id = 91919, cat_id = 4
role_id = 91919, cat_id = 5

UserCategories Table Data:
user_id = 11111, cat_id = 1
user_id = 11111, cat_id = 2
user_id = 11111, cat_id = 3
user_id = 11111, cat_id = 5

Results i get with query above:
cat_id = 1, parent_id = 0
cat_id = 2, parent_id = 1
cat_id = 3, parent_id = 2
cat_id = 5, parent_id = 4

Results i want:
cat_id = 1, parent_id = 0
cat_id = 2, parent_id = 1
cat_id = 3, parent_id = 2
cat_id = 4, parent_id = 3
cat_id = 5, parent_id = 4

SQL gods, please help me!

[guido2004]

SELECT 
    Categories.cat_id
  , Categories.parent_id 
FROM categories Categories 
LEFT OUTER JOIN user_categories UserCategories 
ON Categories.cat_id = UserCategories.cat_id 
LEFT OUTER JOIN role_categories RoleCategories 
ON Categories.cat_id = RoleCategories.cat_id 
WHERE UserCategories.user_id = 11111 
AND RoleCategories.role_id = 91919

I've formatted your query a bit, because 1-line-queries are impossible to read and understand.

In your query you use left joins, and then put criteria on them in the WHERE clause, which makes them act like INNER joins. If you really want to have a LEFT join, put the where clauses in the respective ON clauses.
And if you only want results where the join actually returns a row from the second table, use an INNER join.

SELECT 
    Categories.cat_id
  , Categories.parent_id 
FROM categories Categories 
INNER JOIN role_categories RoleCategories 
ON Categories.cat_id = RoleCategories.cat_id 
LEFT OUTER JOIN user_categories UserCategories 
ON Categories.cat_id = UserCategories.cat_id 
AND UserCategories.user_id = 11111 
WHERE RoleCategories.role_id = 91919

I don't understand what you are trying to accomplish though. If you don't care about the usercategories (you don't select a column from that table, and you don't care if the cat_id is present in that table) then why are you joining it?

[francisnrb]

It's not really that i don't care about UserCategories. If the UserCategories Data would be:

UserCategories Table Data:
user_id = 11111, cat_id = 1
user_id = 11111, cat_id = 2
user_id = 11111, cat_id = 3
user_id = 11111, cat_id = 5
user_id = 11111, cat_id = 6

And RoleCategories stay the same:

Result should be:
cat_id = 1, parent_id = 0
cat_id = 2, parent_id = 1
cat_id = 3, parent_id = 2
cat_id = 4, parent_id = 3
cat_id = 5, parent_id = 4
cat_id = 6, parent_id = 5

I'll try your query and see if it gets the desired results.

[guido2004]

Ok, so you want all categories that are present in at least one of the other two tables.
Then the query would become:

SELECT 
    Categories.cat_id
  , Categories.parent_id 
FROM categories Categories 
LEFT OUTER JOIN user_categories UserCategories 
ON Categories.cat_id = UserCategories.cat_id 
AND UserCategories.user_id = 11111 
LEFT OUTER JOIN role_categories RoleCategories 
ON Categories.cat_id = RoleCategories.cat_id 
AND RoleCategories.role_id = 91919

With your WHERE criteria in the respective ON clauses like I explained before.

[francisnrb]

I tried the second query you sent and it returned a lot of undesired results. The first one worked for me though.

[guido2004]

The first one works for this particular situation, but not for the second situation you described.
My second query isn't correct because it gives all rows in the categories table, and you want only those present in at least one of the two joined tables. I forgot to check that:

SELECT 
    Categories.cat_id
  , Categories.parent_id 
FROM categories Categories 
LEFT OUTER JOIN user_categories UserCategories 
ON Categories.cat_id = UserCategories.cat_id 
AND UserCategories.user_id = 11111 
LEFT OUTER JOIN role_categories RoleCategories 
ON Categories.cat_id = RoleCategories.cat_id 
AND RoleCategories.role_id = 91919
WHERE UserCategories.cat_id IS NOT NULL
OR RoleCategories.cat_id IS NOT NULL

See the WHERE clause? It checks if at least one of the joined tables returns a value that is not NULL, in other words if a row has been found.

[francisnrb]

It still returned undesirable results but lesser now.

[guido2004]

Which undesirable results?

[francisnrb]

Some of the role_id's are NULL and it seems like it got all that have the user_id.

[guido2004]

Please give the exact data of the rows that are being selected and that are undesirable.
Do a SELECT * to see the complete result of the joins