Subquery in Select clause

bla4free · #1 (**permalink**) 07-19-10, 14:55

What I'd like to do are get results like this:

Code:

AGENCY_NUM | DEFENDANTS
-----------+---------------
2010-66096 | Jones, Smith, Wolfe

Here is my data:

Code:

CASE TABLE
id | agency_num
---+-----------
1  | 2010-44570
2  | 2010-40569
3  | 2010-66096

CASE_RELATIONSHIPS TABLE
case_id | people_id
--------+----------
3       | 15
3       | 16
3       | 23

PEOPLE TABLE
id | last_name
---+----------
15 | Smith
16 | Jones
23 | Wolfe

This is the query I have, but I get an error saying my subquery returns more than one row.

Code:

SELECT agency_num, (SELECT people.last_name FROM people) AS defendants

FROM case LEFT JOIN  case_relationships ON case.id = case_relationships.case_id

ORDER BY agency_num

Is this type of query possible? Maybe a subquery is not the right method to do this. Thanks!

dav1mo · #2 (**permalink**) 07-19-10, 15:18

try recursive query or pivot, you should find what you seek.
Dave

r937 · #3 (**permalink**) 07-19-10, 16:01

Code:

SELECT agency_num
     , GROUP_CONCAT(people.last_name) AS defendants
  FROM case 
LEFT OUTER
  JOIN case_relationships 
    ON case_relationships.case_id = case.id 
LEFT OUTER
  JOIN people
    ON people.id = case_relationships.people_id
GROUP
    BY agency_num

bla4free · #4 (**permalink**) 07-19-10, 16:32

Quote:

Originally Posted by r937

Code:

SELECT agency_num
     , GROUP_CONCAT(people.last_name) AS defendants
  FROM case 
LEFT OUTER
  JOIN case_relationships 
    ON case_relationships.case_id = case.id 
LEFT OUTER
  JOIN people
    ON people.id = case_relationships.people_id
GROUP
    BY agency_num

That works perfectly! I think all I really needed was to know about the GROUP_CONCAT function. I've never heard of that before but it looks like I can use it in several places now. Thanks a lot!

Internet Services

Web Development

Digital Marketing

Consumer Tech