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

    Unanswered: Difficulty with query...

    I'm trying to use GROUP_CONCAT to combine results within my query. Basically, the result I want is this:

    Code:
    agency_number | case_type_id | defatty     | prosatty
    --------------+--------------+-------------+-----------------
    2010-25532    | standard     | 16,21,163   | 18,56,63
    Here my query:

    Code:
    select cases_details.agency_number, cases_details.case_type_id,
    group_concat(distinct def_rel.people_id) as defatty, group_concat(distinct pros_rel.people_id) as prosatty
    
    from cases_details
    left join cases_relationships as def_rel on cases_details.id = def_rel.cases_details_id
    left join cases_relationships as pros_rel on cases_details.id = pros_rel.cases_details_id
    
    where pros_rel.role_id = 'prosecutor' and def_rel.role_id = 'defense'
    
    group by agency_number, case_type_id
    I have three tables basically.

    cases_details holds a broad range of information about a single case (case number, case type, etc.).
    people holds detailed information about people (last name, first name, etc.).
    cases_relationships holds the relationship for people who are involved in a case.

    My problem is, my query works for the most part. The correct information is returned. The problem I'm having is if a case does not contain any defattys or prosattys, then the cases are not included in the results. I thought null would be returned, but that doesn't seem to be the case. What am I doing wrong? Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bla4free View Post
    What am I doing wrong? Thanks!
    two things

    first, you have conditions in the WHERE clause which negate the concept of the LEFT OUTER JOINs, which return NULLs for unmatched rows... NULLs are not equal to anything, so the WHERE clause ensures only inner join results are returned

    second, you have cross join effects, and require DISTINCT to collapse them, which is inefficient

    Code:
    SELECT cases_details.agency_number
         , cases_details.case_type_id
         , defense.atty    AS defatty
         , prosecutor.atty AS prosatty
      FROM cases_details
    LEFT OUTER
      JOIN ( SELECT cases_details_id
                  , GROUP_CONCAT(people_id) AS atty
               FROM cases_relationships 
              WHERE role_id = 'defense'
             GROUP
                 BY cases_details_id ) AS defense
        ON defense.cases_details_id = cases_details.id 
    LEFT OUTER
      JOIN ( SELECT cases_details_id
                  , GROUP_CONCAT(people_id) AS atty
               FROM cases_relationships 
              WHERE role_id = 'prosecutor'
             GROUP
                 BY cases_details_id ) AS prosecutor
        ON prosecutor.cases_details_id = cases_details.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    If it's not too much trouble, could you explain what is going on in the left outer joins? I'm a little confused as to why this works. Thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the left outer joins are joining to derived tables (which is what you call subqueries in the FROM clause)

    each of these has only one row per cases_details.id being joined, and this is accomplished by the GROUP BY clauses in the subqueries

    it is as though the subqueries, inside the parentheses, are creating little temporary tables (they last only for the duration of the query)

    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
  •