MySQL Selecting from multiple rows

Question

Using MySQL I am trying to pull ALL people who's company_id matches 3 and also compare whether that person exists in the favourites table.

What I have noticed is that if a person has no job_id then they are not output by the below query.

The below query outputs 1 row, but in the people table there are 3 rows that match the company_id of 3

                SELECT *,
                           j.company_id as companyid,
                           f.id IS NOT NULL AS jid,
                           p.id as pid,
                           f.id AS fave_id,
                           f.id IS NOT NULL 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
                       AND f.user_id = 12
                     WHERE p.company_id = 3
                  ORDER BY p.id ASC

SQLFiddle

Any help is appreciated to get the query to output all rows that match and also check the favourites table.

you are doing an inner join on job by the job_id but don't have matching values on the person table and by matching values i mean there is a blank empty string for one row you want to see with the ON condition — John Ruddell
– John Ruddell, Commented Aug 18, 2014 at 15:53

Donal · Accepted Answer · 2014-08-18 16:02:46Z

1

If you put a left join on job, it will show people that have no job_id. I have updated the fiddle here.

SELECT
    p.*,
    j.company_id as companyid,        
    f.id IS NOT NULL AS jid,
    p.id as pid,
    f.id AS fave_id,
    f.id IS NOT NULL AS fave
FROM people p  
LEFT JOIN job j 
       ON j.id = p.job_id
LEFT JOIN favourites f 
       ON f.people_id=p.id
      AND f.user_id = 12
WHERE p.company_id = 3
ORDER BY p.id ASC

edited Aug 18, 2014 at 16:02

answered Aug 18, 2014 at 15:53

Donal

33.2k10 gold badges69 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Donal Over a year ago

There were some invalid dates in the data. I have updated the schema, see here: sqlfiddle.com/#!2/7fd799/10/0

Ed Brissenden · Accepted Answer · 2014-08-18 16:03:22Z

1

You should change your INNER JOIN for the job table to a LEFT OUTER JOIN so they're selected regardless of whether or not they exist in the job table.

edited Aug 18, 2014 at 16:03

answered Aug 18, 2014 at 15:56

Ed Brissenden

3093 silver badges14 bronze badges

3 Comments

John Ruddell Over a year ago

LEFT JOIN and LEFT OUTER JOIN are synonymous... but thats not the issue here.

Ed Brissenden Over a year ago

The user's example used an INNER JOIN, which is what I suggested should be changed to a LEFT JOIN.

John Ruddell Over a year ago

No i understand that.. if you look at the data the left join wouldn't make much a difference unless you specify NOT NULL in the select like the other answer... because there is a null value for the job_id and an empty string for the job_id in the favorites table

Collectives™ on Stack Overflow

MySQL Selecting from multiple rows

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related