CODEWITHSUNDEEP
mysqlleft-join
Andreas Baran
Andreas Baran

Reputation: 659

MySQL not the expected output - YEAR() won't work

I have a problem with a query which dosen't output what I expects and I cant get it to work.

The query is

SELECT COUNT(event_type_id), userprofiles.id as userprofile_id, users.first_name, users.last_name 
FROM users, userprofiles
            LEFT JOIN event_matches ON event_matches.userprofile_id = userprofiles.id
            AND event_type_id = 1
            LEFT JOIN activities ON activities.id = event_matches.activity_id
            AND YEAR(activities.start) = 2012
            WHERE userprofiles.home_id = 2
            AND users.id = userprofiles.user_id
            GROUP BY userprofiles.id
            ORDER BY COUNT(event_type_id) DESC

But when I run this query it gets all the activities and not only the ones from 2012, it seems like the LEFT JOIN does something. If i dont use LEFT JOIN and instead use JOIN, the query works, but then I only get the user who has a event_type_id = 1 in 2012. But the reason why I used the LEFT JOIN is because I want to have alle the users, and not only the ones who has a event_type_id.

Hopes someone can see my mistake and help me.

Upvotes: 0

Views: 31

Answers (1)

DRapp
DRapp

Reputation: 48139

So what -- in English are you trying to get... Ex: For all user profiles where home_id = 2, how many events of type 1 were within 2012. By doing left joins, you are saying I want all profiles of Home_ID = 2 regardless of attending an event of type 1. You would want to change them from LEFT JOINs to just JOIN (INNER JOIN) if that is the case

This is for all user profiles regardless of an actual event that was of type 1 within 2012, this only cares if it finds an event of type 1

SELECT 
      COUNT(event_type_id), 
      userprofiles.id as userprofile_id, 
      users.first_name, 
      users.last_name 
   FROM 
      userprofiles
         JOIN users
            ON userprofiles.user_id = users.id
         LEFT JOIN event_matches 
            ON userprofiles.id = event_matches.userprofile_id
            AND event_matches.event_type_id = 1
            LEFT JOIN activities 
               ON event_matches.activity_id = activities.id 
               AND YEAR(activities.start) = 2012
   WHERE 
      userprofiles.home_id = 2
   GROUP BY 
      userprofiles.id
   ORDER BY 
      COUNT(event_type_id) DESC

by changing to the granular activity table, you would get your count of event types = 1 AND the year was of 2012

SELECT 
      COUNT(activities.id), 
      ... rest of query...
  ORDER BY 
      COUNT(activities.id) DESC

If you only want those profiles that actually HAD events of type 1 in the year 2012, change to JOIN

SELECT 
      COUNT(event_type_id), 
      userprofiles.id as userprofile_id, 
      users.first_name, 
      users.last_name 
   FROM 
      userprofiles
         JOIN users
            ON userprofiles.user_id = users.id
         JOIN event_matches 
            ON userprofiles.id = event_matches.userprofile_id
            AND event_matches.event_type_id = 1
            JOIN activities 
               ON event_matches.activity_id = activities.id 
               AND YEAR(activities.start) = 2012
   WHERE 
      userprofiles.home_id = 2
   GROUP BY 
      userprofiles.id
   ORDER BY 
      COUNT(event_type_id) DESC

Upvotes: 1

Related Questions