Multiple Left Joins Returning too much data

[rklockner]

I have a query

 

SELECT id, name

FROM a

LEFT JOIN b ON b.bid = a.bid

LEFT JOIN c ON c.cid = a.cid

LEFT JOIN d ON d.did = a.did

.

.

.

LEFT JOIN k ON k.kid = a.kid

WHERE a.id = 5

 

table b returns 3 results.

table c returns 5 results.

table d returns 25 results.

.

.

.

table k returns 10 results.

 

 

What I would like is to know the number of d.did records are returned.

 

I have tried using GROUP BY d.did and COUNT(d.did) as num_results, but I need the multiple records that are returned from the other tables.

 

I'm sure the solution exists (I have tried searching, but I am not sure how to word the search), so any help is appreciated.

 

Thanks!

[requinix]

Do a LEFT JOIN on a subquery that only counts records in d.

LEFT JOIN (
    SELECT did, COUNT(1) AS count FROM d GROUP BY did
) d ON d.did = a.did

You'll want an IFNULL() in there...

[fenway]

Then why not add COUNT(d.did) to the select list?