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.

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
—
2013-05-07T08:50:11Z —
#3

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

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

francisnrb
—
2013-05-07T09:00:30Z —
#5

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

guido2004
—
2013-05-07T09:10:35Z —
#6

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
—
2013-05-07T09:13:20Z —
#7

It still returned undesirable results but lesser now.

guido2004
—
2013-05-07T09:35:56Z —
#8

Which undesirable results?

francisnrb
—
2013-05-07T09:40:05Z —
#9

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

guido2004
—
2013-05-07T09:43:02Z —
#10

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