Thread: Query Issue

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0

    Query Issue


    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:

    Class 101
    Customer A
    Affiliate X
    Affiliate Y
    Affiliate Z
    Customer B
    Affiliate D

    Class 102
    Customer F
    Affiliate J
    etc etc
    Last edited by RedGinger; November 21st, 2012 at 06:22 AM. Reason: made edits to correct sql
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    you seem to be missing the "t" table from your FROM clause

    which database system is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    you seem to be missing the "t" table from your FROM clause

    which database system is this?
    Just edited SQL...my mistake
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    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.

IMN logo majestic logo threadwatch logo seochat tools logo