all ids are PK. I want to build a sql query that finds a user, which belongs to a specified dataid, by its uname and checks if the user is in a group (relation in table user_in_group) belonging to a specified package (a group is assigned to one package). if so data from users, package and group should be fetched, otherwise only the user data should be fetched. Therefore I use left joins, so I can also get the users with no group:

SELECT `uac`.`uid`, `u`.`uid`, `uig`.`groupid`, `ag`.`packageid`
FROM `user_has_data` AS `uac`
INNER JOIN `users` AS `u` ON u.uid = uac.uid
LEFT JOIN `user_in_group` AS `uig` ON uig.uid = uac.uid
LEFT JOIN `groups` AS `ag` ON (ag.groupid = uig.groupid) AND (ag.packageid = 2)
WHERE (uac.dataid = '3') AND (u.uname LIKE 'test%')
GROUP BY `u`.`uid`

Unfortunately I get wrong results: I get groups that have a different packageid than stated in the join, if the user has another group assigned to him with a different packageid.

probably this is because the first left join has no restrictions to packageid and the second is a left join and so it has no restrictions on the result (packageid is NULL for all results, but should have values). If I change the second left join to a ordinary join, the group problem would be fixed but the query cant find users without group any more.

SELECT `uac`.`uid`, `u`.`uid`, `uig`.`groupid`, `ag`.`packageid`
FROM `user_has_data` AS `uac`
INNER JOIN `users` AS `u` ON u.uid = uac.uid
LEFT OUTER JOIN `user_in_group` AS `uig` ON uig.uid = uac.uid
LEFT OUTER JOIN `groups` AS `ag` ON ag.groupid = uig.groupid
WHERE (uac.dataid = '3') AND (u.uname LIKE 'test%')
AND (ag.packageid = 2 OR uig.uid IS NULL)
GROUP BY `u`.`uid`

I know LEFT JOIN and LEFT OUTER JOIN mean the same thing, but I like to be explicit. With the condition in your join, I bet you were getting groups with different packages, but weren't getting the packages?