COUNT, Group By with LEFT JOIN and JOIN

[AlexHWGUY]

SELECT s.siteName, count(p.posNo) as nbPos
FROM site s
LEFT JOIN pos p ON s.siteId = p.siteId
AND posDTime >= CONVERT_TZ(TIMESTAMP('2009-03-01'),'SYSTEM','GMT')
AND posDTime <= DATE_ADD(CONVERT_TZ(TIMESTAMP('2009-03-04'),'SYSTEM','GMT'), INTERVAL 1 DAY)

AND p.mobileId IN (SELECT mobileId FROM callsign WHERE cieNo = '5')
/*JOIN callsign c ON c.mobileId = cp.mobileId AND c.cieNo = '5'*/

GROUP BY s.siteId ORDER BY nbPos DESC

 

Table : pos, site, callsign

This query return the number of "Position (pos)" per site for a Periode and a Cie

 

It's working but I think the sub-query slow query time.

Is there a way to integrate the "JOIN callsign" in the query without loosing count=0 results ?

 

Thanks !

 

 

 

[fenway]

I don't understand what you're trying to do.