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

    Unanswered: Subquery in Select clause

    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!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try recursive query or pivot, you should find what you seek.
    Dave

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by r937 View Post
    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!

Posting Permissions

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