Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Looping through distinct records...

    I don't know how to word this so I'll give it my best shot. I have two tables: a "charges" table and a "people" table:

    Code:
    C H A R G E S
    
    CASE_ID  PEOPLE_ID  CHARGE_ID
    1        3          16
    1        3          18
    1        3          21
    1        3          32
    1        7          16
    1        7          18
    1        7          21
    1        9          16
    1        9          11
    Code:
    P E O P L E
    
    PEOPLE_ID  NAME
    3          John Doe
    7          Jane Smith
    9          Duncan Mann
    Basically, I want to loop through all these charges and display the distinct charges, with the names of each person associated with those charges. Is this possible? Basically, my results would look like this:

    Code:
    R E S U L T S
    
    CHARGE_ID   NAME
    16          John Doe, Jane Smith, Duncan Mann
    18          John Doe, Jane Smith
    21          John Doe, Jane Smith
    32          John Doe
    11          Duncan Munn
    How can I accomplish this? I don't even know where to start. Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT charges.charge_id
         , GROUP_CONCAT(people.name) AS names
      FROM charges
    INNER
      JOIN people
        ON people.people_id = charges.people_id
    GROUP
        BY charges.charge_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't loop, use SQL as R937 has suggested
    looping is less efficient than SQL and will almost certainly cost more in terms of processor time, network bandwidth
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by r937
    Code:
    SELECT charges.charge_id
         , GROUP_CONCAT(people.name) AS names
      FROM charges
    INNER
      JOIN people
        ON people.people_id = charges.people_id
    GROUP
        BY charges.charge_id
    Thanks! That works almost perfectly. If I have a person that has multiple of the same charge_id, their name will appear multiple times. Is there a way to prevent that? Thanks!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um... could you repeat that please?

    the query aggregates on charge_id, so multiple people are concatenated into the names column, one row per charge_id

    what did you get that looked wrong to you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2005
    Posts
    165
    When I applied it in my situation, it worked almost perfectly. I guess one aspect I left out is people can have multiple of the same charges:

    Code:
    C H A R G E S
    
    CASE_ID  PEOPLE_ID  CHARGE_ID
    1        3          16
    1        3          16
    1        3          18
    1        3          21
    1        3          32
    1        7          16
    1        7          18
    1        7          21
    1        9          16
    1        9          11
    Code:
    P E O P L E
    
    PEOPLE_ID  NAME
    3          John Doe
    7          Jane Smith
    9          Duncan Mann
    Code:
    R E S U L T S
    
    CHARGE_ID   NAME
    16          John Doe, John Doe, Jane Smith, Duncan Mann
    18          John Doe, Jane Smith
    21          John Doe, Jane Smith
    32          John Doe
    11          Duncan Munn
    I've modified my example from above to show you what I get. Sometimes, people can have 4 or 5 of the same charges. Does that make more sense? Thanks!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bla4free
    Does that make more sense?
    no, less

    why would you allow that?

    "people can have multiple of the same charges"

    why???

    if you store a fact twice, does that make it more believable as a fact?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2005
    Posts
    165
    This is a case management system for a prosecutor's office. The charge_id's represent the various crimes the individual person committed. A person can be charged with multiple counts of armed robbery.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bla4free
    I guess one aspect I left out is ...
    i guess you left out a lot more than you left in, eh
    Code:
    SELECT charge_id
         , GROUP_CONCAT(name) AS names
      FROM (
           SELECT DISTINCT
                  charges.charge_id
                , people.name
             FROM charges
           INNER
             JOIN people
               ON people.people_id = charges.people_id
           ) AS d
    GROUP
        BY charge_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •