Trouble with query

[jcombs_31]

Hey guys.  I posted this on stack overflow but I'm still not getting exactly the results I want .  In the end I my just use PHP to put together an array for me, but I'd prefer to get this direct from the SQL query.

 

Instead of typing it all out, here is the issue:  http://stackoverflow.com/questions/8647445/join-not-showing-all-rows

 

I'm charting this with google charts.  I was reading this http://www.richnetapps.com/using-mysql-generate-daily-sales-reports-filled-gaps/ but I think my query is a bit more complex because of the groupings.  Any thoughts on the best way to go about this would be appreciated.

[jcombs_31]

I've tried this

 

SELECT WEEK(Calendar.datefield) AS Week, CONCAT(Employee.firstname, ' ', Employee.lastname) as Name, AVG(Rating.scale) as Average
FROM calendar Calendar LEFT JOIN reviews Review ON WEEK(Calendar.datefield) = WEEK(Review.created)
LEFT JOIN employees Employee ON Review.reviewee_id = Employee.id AND Employee.id IN (71, 72, 73)
INNER JOIN ratings AS Rating on Rating.id = Review.rating_id
WHERE Calendar.datefield BETWEEN '2011-09-01' AND '2011-12-31'
GROUP BY Week, Employee.id
ORDER BY Week, Employee.id

 

which gets me close but the employee field is NULL rather than the rating, which doesn't quite make sense

[mikosiko]

but the employee field is NULL rather than the rating, which doesn't quite make sense

 

make perfect sense to me... you are LEFT joining the calendar table with the reviews table, therefore if in any week there are no records in the table reviews your reviewee_id field is going to be NULL, hence the JOIN with the employees table will produce as result the employee name with NULL value.

 

In your scenario the only way that your select is going to work and give the result that you are expecting is if for each week and for each employee there are at least 1 record in the table reviews with the field rating_id NULL, ZERO or any other value non-existent in the table ratings. The other probable solution is get only the data available with the SELECT, and post-process at display time (using some array and some logic to fill the missing records for a week).

[jcombs_31]

but the employee field is NULL rather than the rating, which doesn't quite make sense

 

make perfect sense to me... you are LEFT joining the calendar table with the reviews table, therefore if in any week there are no records in the table reviews your reviewee_id field is going to be NULL, hence the JOIN with the employees table will produce as result the employee name with NULL value.

 

In your scenario the only way that your select is going to work and give the result that you are expecting is if for each week and for each employee there are at least 1 record in the table reviews with the field rating_id NULL, ZERO or any other value non-existent in the table ratings. The other probable solution is get only the data available with the SELECT, and post-process at display time (using some array and some logic to fill the missing records for a week).

 

Thanks for the response.  I think in the end I'll have to use some application logic to get the results I want.  I was hoping I could do this with SQL alone, but based on my needs it seems that may not be possible.

[The Little Guy]

I would suggest removing the where an placing it in the join:

 

I have had a similar problem, and doing that has worked for me.

 

SELECT WEEK(Review.created)                               AS Week,
       CONCAT(Employee.firstname, ' ', Employee.lastname) AS Name,
       AVG(Rating.scale)                                  AS Average
FROM   employees Employee
       LEFT JOIN reviews Review
         ON (Employee.id = Review.reviewee_id and Employee.id IN ( 71, 72 ))
       LEFT JOIN ratings AS Rating
         ON Rating.id = Review.rating_id
GROUP  BY WEEK(Review.created),
          Employee.id
ORDER  BY WEEK(Review.created),
          Employee.id

[jcombs_31]

I would suggest removing the where an placing it in the join:

 

I have had a similar problem, and doing that has worked for me.

 

SELECT WEEK(Review.created)                               AS Week,
       CONCAT(Employee.firstname, ' ', Employee.lastname) AS Name,
       AVG(Rating.scale)                                  AS Average
FROM   employees Employee
       LEFT JOIN reviews Review
         ON (Employee.id = Review.reviewee_id and Employee.id IN ( 71, 72 ))
       LEFT JOIN ratings AS Rating
         ON Rating.id = Review.rating_id
GROUP  BY WEEK(Review.created),
          Employee.id
ORDER  BY WEEK(Review.created),
          Employee.id

 

Adding the condition to the WHERE clause would cause ALL employees to be returned.  In the end I just ran a query for each employee and combined them into a single array to use for my line graph.  There is not much overhead as generally only a few employees will be selected and I can guarantee my results this way.

[Muddy_Funster]

I would suggest removing the where an placing it in the join:

 

I have had a similar problem, and doing that has worked for me.

 

SELECT WEEK(Review.created)                               AS Week,
       CONCAT(Employee.firstname, ' ', Employee.lastname) AS Name,
       AVG(Rating.scale)                                  AS Average
FROM   employees Employee
       LEFT JOIN reviews Review
         ON (Employee.id = Review.reviewee_id and Employee.id IN ( 71, 72 ))
       LEFT JOIN ratings AS Rating
         ON Rating.id = Review.rating_id
GROUP  BY WEEK(Review.created),
          Employee.id
ORDER  BY WEEK(Review.created),
          Employee.id

 

Adding the condition to the WHERE clause would cause ALL employees to be returned.  In the end I just ran a query for each employee and combined them into a single array to use for my line graph.  There is not much overhead as generally only a few employees will be selected and I can guarantee my results this way.

 

[jcombs_31]

I would suggest removing the where an placing it in the join:

 

I have had a similar problem, and doing that has worked for me.

 

SELECT WEEK(Review.created)                               AS Week,
       CONCAT(Employee.firstname, ' ', Employee.lastname) AS Name,
       AVG(Rating.scale)                                  AS Average
FROM   employees Employee
       LEFT JOIN reviews Review
         ON (Employee.id = Review.reviewee_id and Employee.id IN ( 71, 72 ))
       LEFT JOIN ratings AS Rating
         ON Rating.id = Review.rating_id
GROUP  BY WEEK(Review.created),
          Employee.id
ORDER  BY WEEK(Review.created),
          Employee.id

 

Adding the condition to the WHERE clause would cause ALL employees to be returned.  In the end I just ran a query for each employee and combined them into a single array to use for my line graph.  There is not much overhead as generally only a few employees will be selected and I can guarantee my results this way.

 

 

Which part?

[Muddy_Funster]

it is suggested that you remove the where clause and include the conditional lookup within the join, yet you say that "adding" it to the where clause would return "all records".  That makes no sense.  if you added that SQL to your where clause you would get nothing but errors.  On top of that, how does adding conditions to a where clause cause all records to be returned (with the obvious exception of a stupid OR stuck in for a giggle)? 

 

Your reply is nonsensical from start to finnish, so in answer - all of it.

[fenway]

TLDR -- but in general, if you want to example the values of columns in a table being LEFT JOIN-ed, you need to move it to the ON clause, or the WHERE will always fail.

[jcombs_31]

it is suggested that you remove the where clause and include the conditional lookup within the join, yet you say that "adding" it to the where clause would return "all records".  That makes no sense.  if you added that SQL to your where clause you would get nothing but errors.  On top of that, how does adding conditions to a where clause cause all records to be returned (with the obvious exception of a stupid OR stuck in for a giggle)? 

 

Your reply is nonsensical from start to finnish, so in answer - all of it.

 

The whole point in the left join is to return all records from the left table regardless of a match on the right table.  I want to query only specific employees, not all employees. I've tried adding the to the ON, and I ALWAYS get all employees.

 

My understanding is that you'd use the ON clause for any condition that is not on the left table.