Problem using "WITH ROLLUP"

[dough boy]

My query:

 

SELECT cp.name, SUM(qty) 
FROM course_selections cs, course_options co, client_partnerships cps, client_partners cp
WHERE cs.course_id = co.course_id
AND co.provider_id = 6
AND cs.client_id > 1
AND cs.client_id = cps.client_id
AND cps.client_partner_id = cp.id
AND cps.`primary` = 1
GROUP BY cp.name ASC;

 

Returns the correct results:

 

name  SUM( qty )
A1 	1018
A2 	27
A3	606
A4	3101
A5 	339

 

 

However, as soon as I apply the "WITH ROLLUP" modifier the results returned are:

 

name  SUM( qty )
A1 	44
A2 	39
NULL    83

 

I looked at the documentation and other sites I could find, but no where does it mention that "WITH ROLLUP" will only work if there is one table.  Is this just understood or am I missing something?

[artacus]

Try doing with JOIN statements instead.

[dough boy]

It is already using "inner" joins.  What joins are you referring to?

[artacus]

No,  you are using a bastardization of a join.  Mostly mysql lets you get away with it, but quirky things happen.

 

Correct syntax would be:

SELECT cp.name, SUM(qty) AS quantity
FROM course_selections AS cs
JOIN course_options AS co ON cs.course_id = co.course_id
JOIN client_partnerships AS cps ON  cs.client_id = cps.client_id
JOIN client_partners cp ON cps.client_partner_id = cp.id AND cps.`primary` = 1
WHERE co.provider_id = 6 AND cs.client_id > 1
GROUP BY cp.name WITH ROLLUP;

[dough boy]

I am sorry, but you are incorrect.  This is even how MySQL recommends writing queries.  I can't think what the standard is called now (it is late).  They also say what you did would be correct as well (although technically you should have the word "inner" before your joins).  Some might say yours is better because you can "see" all of the joins and make sure you have everything, but I will stick with this way.

 

Also, that is not the problem.  I ran your query and got the exact same results as with my query. 

 

I am starting to think that the WITH ROLLUP is only designed to work when you are dealing with 1 table, but it is not mentioned.

 

Thanks for helping.

[artacus]

So you're going to school? I'm not the one asking for help.

 

Trust me mysql has problems when using your syntax. Especially with table aliases. I wasn't sure that was what the problem was in your situation.

 

WITH ROLLUP does work with joined tables, that's not the problem.

[dough boy]

No I am not in school.  I have a masters and have been doing this for many years.  Not to mention I have attended numerous "training" seminars, conferences, and even had MySQL experts (yes the ones that write behind the scenes) , and they all have said that the way we write queries is 100% fine.  The even said it is one of the 2 "standards".

 

MySQL does not have problems with table aliases.

 

Here is some information for your reading pleasure (so far all I can find is info about oracle, but it is the same syntax).  I believe the 2 "correct" standards are the ANSI and ODBC.

 

One looks like this:

 

SELECT t1.field

FROM table1 t1, table 2 t2

WHERE t2.id = t2.id

 

The other one is:

 

SELECT t1.field

FROM table1 t1

INNER JOIN table2 t2 ON t2.id = t1.id

 

They are both syntactically correct.  Both of these are "preached" by MySQL.  Most I know preach #1 to separate the "logic".  There are a few that preach #2 because it will help you if you have a lot of joins when making sure you link all of the fields and do not miss one (causing a "run away" query).

 

http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html

 

Also check out the MySQL profiles.  Several of the "members" have websites you can visit to get a sample of their coding styles (Jay Pipes being one that codes the 2nd way).

 

http://dev.mysql.com/guilds/profiles.html