MySQL Query Multiple Joins with Incorrect Results

Question

I have 3 tables structured like so:

activity table:

activity_id, user_id, type, date

reviews table:

review_id, activity_id, fishery_id, review, date

updates table:

update_id, activity_id, update, date

I want to call the all the reviews and updates which are linked to a user by the activity table however this query returns incorrect results, any ideas?

query('
  SELECT *
  FROM activity as activity    
  LEFT JOIN reviews AS reviews
    ON activity.activity_id = reviews.activity_id    
  LEFT JOIN updates AS updates
    ON activity.activity_id = updates.activity_id    
  WHERE user_id = 1
');

Thanks, Matt

Answer 1

query('
  SELECT *
  FROM activity as activity

  LEFT JOIN reviews AS reviews
    ON activity.activity_id = reviews.activity_id

  LEFT JOIN updates AS updates
    ON reviews.activity_id = updates.activity_id


  WHERE user_id = 1
');

Answer 2

You're trying to select two different sets of data, the only way I can thing of doing this in one SQL statement, is to use a union, and create some extra columns to tell the difference.

SELECT 'review' as type,r.review_id as id,r.fishery_id as fid,
    r.review as review,null as update,r.date as date
FROM reviews r,activity a
WHERE r.activity_id=a.activity_id AND a.user_id=@user_id

UNION

SELECT 'updates' as type,u.update_id as id,null as fid,
    null as review,u.update update,u.date as date
FROM updates u,activity a
WHERE u.activity_id=a.activity_id AND a.user_id=@user_id

Answer 3

If you have to use different types of Joins in your query, use Inner Join. It makes your query more readable. And, Inner Join = Join.

Answer 4

You probably don't want reviews and updates in the same rows, which appears to be what you are trying to do. So, you need two queries, not one, which would be:

SELECT r.review_id, r.fishery_id, r.review, r.date
FROM   reviews r
WHERE  EXISTS (SELECT 1 FROM activity a
               WHERE  a.user_id = @user_id
               AND    a.activity_id = r.activity_id)

SELECT u.update_id, u.update, u.date
FROM   update u
WHERE  EXISTS (SELECT 1 FROM activity a
               WHERE  a.user_id = @user_id
               AND    a.activity_id = u.activity_id)

Answer 5

Just for reference the working query:

  SELECT type, review_id, activity.activity_id, review, activity.date, fishery_id
  FROM activity as activity
  JOIN reviews AS reviews
    ON activity.activity_id = reviews.activity_id

  UNION

  SELECT type, activity_id, activity.activity_id, activity, activity.date, NULL
  FROM activity as activity
  JOIN activity AS activity
    ON activity.activity_id = activity.activity_id

  WHERE activity.user_id = 1

  ORDER BY date DESC