Syntax for combining joins in mysql
- by UltraVi01
I seem to remember reading somewhere that there is a way to combine LEFT JOIN statements into a more simple query. I have the following query and was hoping someone could kindly take a look at it.
SET @userId = 8;
SELECT ug.user_id, COUNT(DISTINCT goal_id) as matches
FROM user_goal ug, user u, profile p
LEFT JOIN user_block ub ON @userId = ub.blocked_id
LEFT JOIN user_block ub2 ON @userId = ub2.blocker_id
LEFT JOIN user_user uu ON @userId = uu.user_id
LEFT JOIN friend_request fr ON @userId = fr.user_id
WHERE ug.user_id = u.id AND u.profile_id = p.id
AND (ub.blocker_id IS NULL OR ub.blocker_id != ug.user_id)
AND (ub2.blocked_id IS NULL OR ub2.blocked_id != ug.user_id)
AND (uu.user_friends_id IS NULL OR uu.user_friends_id != ug.user_id)
AND (fr.to_user_id IS NULL OR (fr.to_user_id != ug.user_id))
AND ug.user_id!=@userId
AND p.suggestible
AND goal_id IN (SELECT iug.goal_id FROM user_goal iug WHERE user_id=@userId)
GROUP BY user_id ORDER BY matches DESC LIMIT 4
