November 20th, 2012, 09:36 PM
I'm have 2 tables and want to group by class, but it's not working...help please
select cl.class, c.customer_ID, cl.affiliate
from class cl, customer c
where c.customer_ID = cl.customer_ID
group by cl.class (not working)
Each class has a group of customers associated with it....
I want to return all the t.affiliate info for each of the customers associated with a class. So, I'd like to see:
Last edited by RedGinger; November 21st, 2012 at 07:22 AM.
Reason: made edits to correct sql
November 21st, 2012, 06:35 AM
you seem to be missing the "t" table from your FROM clause
which database system is this?
November 21st, 2012, 07:23 AM
Just edited SQL...my mistake
Originally Posted by r937
November 21st, 2012, 08:12 AM
this is a misunderstanding of SQL and probably the relational model in general. You cannot have "groups" in the sense of paragraphs or something. The relational model works with tables, so whatever output you want must fit in a table. Any other "grouping" must be done in your application. A simple rule of thumb: When you have a GROUP BY clause without an aggregate function like COUNT() or SUM(), there's something wrong.
I guess what you actually want is simply JOIN the two tables, ORDER them by the class ID (to make sure the rows actually appear in "groups") and then iterate over the rows in your application to display those class paragraphs.