CODEWITHSUNDEEP
phpmysqljoin
Fred Turner
Fred Turner

Reputation: 139

mysql left join and union?

I have this left join query to show all job listings to employees who are in an asigned group of a particular supervisor, it works great

SELECT * 
FROM jobs j 
LEFT JOIN groups g ON g.group_number = j.relevant_group 
WHERE j.relevant_group = 0 
OR (g.supervisor_id = j.job_lister_id AND g.employee_id = '$myVar')

But, now I need to add in a selection so that the posting supervisor can see it also, I set up the $ myVar to hold the session id of the viewer. I tried this

SELECT * 
FROM jobs j 
LEFT JOIN groups g ON g.group_number = j.relevant_group 
WHERE j.relevant_group = 0 
OR (g.supervisor_id = j.job_lister_id AND g.employee_id = '$myVar')
OR (j.job_lister_id = '$myVar')

But when the supervisor logs in he sees the listing posted numerous times (the duplication amount always equals the number of people in the group) how do i fix that?

Upvotes: 0

Views: 355

Answers (1)

ruakh
ruakh

Reputation: 183602

Do you actually need any fields from groups to be in the result set? Because it sounds like you're just using groups to identify which jobs records to return? If that's the case, then you should probably dispense with the JOIN, and use a subquery instead. For example, you might write:

SELECT * 
  FROM jobs j
 WHERE j.job_lister_id = '$myVar'
    OR j.relevant_group = 0
    OR EXISTS
        ( SELECT 1
            FROM groups g
           WHERE g.group_number = j.relevant_group
             AND g.supervisor_id = j.job_lister_id
             AND g.employee_id = '$myVar'
        )
;

Upvotes: 1

Related Questions