...

View Full Version : need some left join help



Leeoniya
04-05-2009, 06:22 AM
i'd like to get all users with their order counts and order totals, even if the users have no orders. this is what i got and it only shows users who have orders in the specified time period...like an INNER JOIN : (


SELECT
COUNT(*) AS count,
SUM(total) - SUM(sales_tax) AS total,
u.fname,
u.note_color
FROM
users u
LEFT JOIN orders o ON (u.id = o.sales_rep_id)
WHERE
o.date BETWEEN '2009-04-04' AND '2009-04-05' AND
o.status_id NOT IN (5,6)
GROUP BY
u.id

thanks,
Leon

Old Pedant
04-05-2009, 07:05 AM
Two errors:

(1) Your GROUP BY is wrong. You must GROUP BY the fields that are *NOT* part of the aggregate expression.
(2) Any time you use fields from the DEPENDENT table (the right table in a LEFT JOIN, left table in a RIGHT JOIN) in your WHERE clause, you *automatically* convert the outer join into an INNER JOIN!!!

To see why, look at the third post in this thread:
http://www.aspmessageboard.com/showthread.php?t=230397

SO...



SELECT
u.id,
u.fname,
u.note_color,
COUNT(o.total) AS count,
SUM(o.total) - SUM(o.sales_tax) AS total
FROM
users u
LEFT JOIN orders o ON (
u.id = o.sales_rep_id
AND o.date BETWEEN '2009-04-04' AND '2009-04-05'
AND o.status_id NOT IN (5,6)
)
GROUP BY u.id, u.fname, u.note_color


I added u.id to the SELECT list because it occurred to me that you could have two u.fname values that are the same, with the same u.note_color values, too.

MySQL may indeed be flexible enough to allow the GROUP BY on only u.id, but it's not ANSI SQL, so I'd strongly recommend going with the standard: Mention *all* non-aggregated fields in your GROUP BY.

Leeoniya
04-05-2009, 09:29 AM
yep, that did it, thanks. i'm not sure it's necessary to group by anything other than u.id...it's unique already

Leeoniya
04-05-2009, 12:11 PM
out of curiosity, is there any way to use a single query perhaps with a CASE to aggregate different date ranges in a single pass? and make month_count/month_total, ytd_count/ytd_total...etc columns. for each sales_rep_id as above

thanks
Leon

Old Pedant
04-05-2009, 10:05 PM
First of all, as I said, MySQL might let you get away with just the GROUP BY u.id, but it's really a bad habit to get into as other DBs will not let you do it.

As to the other...

Sure, but you might find that in the long run you are better off doing that in your PHP/ASP/JSP code.

But, just for an example:


SELECT
u.id,
u.fname,
u.note_color,
SUM( IF(MONTH(o.date)=3,1,0) ) AS marchCount,
SUM( IF(MONTH(o.date)=3, o.total - o.sales_tax, 0 ) ) AS marchTotal
SUM( IF(MONTH(o.date)=4,1,0) ) AS aprilCount,
SUM( IF(MONTH(o.date)=4, o.total - o.sales_tax, 0 ) ) AS aprilTotal,
COUNT( o.date ) AS ytdCount,
SUM( o.total - o.salestax ) AS ytdTotal
FROM
users u
LEFT JOIN orders o ON (
u.id = o.sales_rep_id
AND YEAR(o.date) = 2009
AND o.status_id NOT IN (5,6)
)
GROUP BY u.id, u.fname, u.note_color


You can, of course, use CASE WHEN in place of the IF, but for something like this the IF is smaller and easier to read.

Fumigator
04-06-2009, 05:24 PM
I really hate the fact MySQL allows columns in the SELECT clause that are not part of the GROUP BY clause. The results are random and too often I see people expecting one thing and not understanding why they're not getting what they expect.

Old Pedant
04-06-2009, 08:43 PM
Agreed. Why they decided to go non-ANSI on this simple aspect of queries is beyond me.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum