Looping through distinct records...

bla4free · #1 (**permalink**) 03-16-09, 22:56

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!

r937 · #2 (**permalink**) 03-17-09, 00:49

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

healdem · #3 (**permalink**) 03-17-09, 04:48

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

bla4free · #4 (**permalink**) 03-17-09, 12:17

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!

r937 · #5 (**permalink**) 03-17-09, 12:27

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?

bla4free · #6 (**permalink**) 03-17-09, 21:21

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!

r937 · #7 (**permalink**) 03-17-09, 21:35

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?

bla4free · #8 (**permalink**) 03-18-09, 08:47

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.

r937 · #9 (**permalink**) 03-18-09, 09:00

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

Internet Services

Web Development

Digital Marketing

Consumer Tech