Losing Records in a Left join after using Group by

Question

Basically after executing this query:

SELECT
`view_customer_locations`.customerid,
`view_customer_locations`.community_groupid,
`view_customer_locations`.community_group,


`view_sip_user_agents`.sip_user_agentid,
`view_sip_user_agents`.didid,
`view_sip_user_agents`.temporary_didid,
`view_sip_user_agents`.active_did,
GROUP_CONCAT( (IF(`view_sip_user_agents`.active_did = 'permanent', cast(`permanent_dids`.did  as char(10)), cast(`temporary_dids`.did as char(10)))) SEPARATOR ', ') as did,

`view_sip_user_agents`.sip_user_agents_date_archived

FROM `view_customer_locations`
LEFT JOIN `view_sip_user_agents` on `view_customer_locations`.customerid = `view_sip_user_agents`.customerid
LEFT JOIN `dids` AS permanent_dids ON `view_sip_user_agents`.didid = `permanent_dids`.id
LEFT JOIN `dids` AS temporary_dids ON `view_sip_user_agents`.temporary_didid = `temporary_dids`.id
Group by `view_customer_locations`.customerid

i still want all the rows from the view_customer_locations table.. but i am losing any entries in the view_customer_locations table that don't have a corresponding record in the view_sip_user_agents table. I also want the entries to be grouped by customerid .. so that each customer only has one entry in the resulting query.

If i remove the group by clause, i get all the entires from the view_customer_locations table but naturally i have multiple entries per customer which is not what i want.

please help

Answer 1

Although MySQL does let you "get away" with expressing a GROUP BY clause with fields in the SELECT tha that might conceivable vary over the GROUP BY fields (theoretically picking an "arbitrary/random value"), the results of this ill-conceived, logically not well-founded operation are sometimes surprising, as you've noticed.

Try using correct SQL, e.g. with a MAX operator over the fields you're not "grouping by". If the implied assumption that those fields are strictly determined by the grouped-by fields is right, this can't possibly damage your results in any way, right? And yet sometimes you'll find that results do appear, or change (meaning the implied assumption was, simply, wrong).

In your case, since some of the fields might be uniformly NULL in a group, and MAX in that case is not necessarily well-defined, you might further try to use IFNULL there, of course.

Answer 2

I don't think GROUP BY is what you really want here. DISTINCT is more correct because it will eliminate duplicates but the results are defined on the non-grouped by fields

SELECT DISTINCT
`view_customer_locations`.customerid,
`view_customer_locations`.community_groupid,
`view_customer_locations`.community_group,


`view_sip_user_agents`.sip_user_agentid,
`view_sip_user_agents`.didid,
`view_sip_user_agents`.temporary_didid,
`view_sip_user_agents`.active_did,
GROUP_CONCAT( (IF(`view_sip_user_agents`.active_did = 'permanent', cast(`permanent_dids`.did  as char(10)), cast(`temporary_dids`.did as char(10)))) SEPARATOR ', ') as did,

`view_sip_user_agents`.sip_user_agents_date_archived

FROM `view_customer_locations`
LEFT JOIN `view_sip_user_agents` on `view_customer_locations`.customerid = `view_sip_user_agents`.customerid
LEFT JOIN `dids` AS permanent_dids ON `view_sip_user_agents`.didid = `permanent_dids`.id
LEFT JOIN `dids` AS temporary_dids ON `view_sip_user_agents`.temporary_didid = `temporary_dids`.id
Group by `view_customer_locations`.customerid