CODEWITHSUNDEEP
phpsqlmysqljoin
Matt
Matt

Reputation: 557

MySQL Query Multiple Joins with Incorrect Results

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

Upvotes: 0

Views: 190

Answers (3)

Matt
Matt

Reputation: 557

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

Upvotes: 0

Rudu
Rudu

Reputation: 15892

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

Upvotes: 2

Andrew
Andrew

Reputation: 14447

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)

Upvotes: 1

Related Questions