MySQL高级选择查询检查来自另一个表的数据

Question

I have a Database that I'm struggling to do a query for. The main table is people and what I am trying to do is add onto the end of the select query the id from the favourites table ONLY if the client_member_id matches.

The idea is to loop through each People row and at the end of the row add on the f.id if that persons id is in the favourites table AND it matches the id of the client_member_id.

The client_member_id is an id given to each client so that when they Favourite a person then the details get put to the favourites table. This would be either 1 or 2. In the SQLFiddle example the expected output of the FAVE_ID when client_member_id is 2 would be 68 69 but if client_member_id is 1 then FAVE_ID would both be null

I have gotten it to a point where it seems to duplicate the rows and I need it to be distinct.

SELECT *, j.company_id as companyid, j.id as jid, p.id as pid,
    (CASE WHEN f.client_member_id = 2 AND p.id = f.people_id THEN f.id ELSE null END) AS fave_id,
    (CASE WHEN f.client_member_id = 2 AND p.id = f.people_id THEN 1 ELSE 0 END) AS fave
FROM people p
INNER JOIN favourites f
INNER JOIN job j 
ON p.job_id = j.id
WHERE p.company_id = 1
ORDER BY p.id ASC

SQLFiddle

Thank you in advance.

Answer 1

You can use a LEFT JOIN to favourites to get rid of the ungainly CASE:

    SELECT *,
           j.company_id companyid,
           j.id jid,
           p.id pid,
           f.id fave_id,
           f.id IS NOT NULL fave
      FROM people p    
INNER JOIN job j 
        ON p.job_id = j.id
 LEFT JOIN favourites f
        ON f.people_id=p.id
       AND f.client_member_id=2
     WHERE p.company_id = 1
  ORDER BY p.id ASC

N.B This will return only two rows for your fiddle with fave_id and fave, NOT NULL and 1, but in the event that you have a person without a corresponding favourite then you get fave_id and fave, NULL and 0.