CODEWITHSUNDEEP
phpmysqlsql
ngplayground
ngplayground

Reputation: 21617

MySQL Advanced select query checking data from another table

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.

Upvotes: 0

Views: 76

Answers (3)

Arth
Arth

Reputation: 13110

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.

Upvotes: 1

Liam Wheldon
Liam Wheldon

Reputation: 755

Try this SQLFiddle

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

You didn't bind your favourites table join to anything and therefore you had number of people rows * number of favourites returned. I've used a left join as I'm guessing that you can't guarantee that there will be a favourite for every person in the people table? Else if you can then just change to inner join.

Regards

Liam

Upvotes: 0

LHristov
LHristov

Reputation: 1123

There are no duplicates, you have 4 rows because of fave_id and fave columns. If you want to filter them then here is an example how to show only values for fave=1 :

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 AND f.client_member_id = 2 AND p.id = f.people_id
ORDER BY p.id ASC

Upvotes: 1

Related Questions