Difficulty with query...

bla4free · #1 (**permalink**) 10-12-10, 14:37

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!

r937 · #2 (**permalink**) 10-12-10, 15:04

Quote:

Originally Posted by bla4free

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

bla4free · #3 (**permalink**) 10-12-10, 15:23

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!

r937 · #4 (**permalink**) 10-12-10, 16:21

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech