[SOLVED] cannot show all rows

[mga_ka_php]

How do you correct this statement

 

SELECT a.siteID 'ID', a.siteDomain 'Site', COUNT(*) 'New Members'

FROM sites a LEFT JOIN users b ON a.siteID=b.siteID

WHERE

b.userDateRegistered>='1231513284' AND

b.userDateRegistered<='1231553482'

GROUP BY a.siteID

 

have 2 tables, sites and users, i want to view all the sites with how many members

 

the problem with this code it only display the sites with >0 users

 

i want to show even the 0 users

 

Site        | New Members

site.com  | 0

site2.com | 10

 

 

[Gamic]

SELECT a.siteID 'ID', a.siteDomain 'Site', COUNT(b.*) 'New Members'
FROM sites a LEFT OUTER JOIN users b ON a.siteID=b.siteID
WHERE
(b.userDateRegistered between '1231513284' and '1231553482')
or b.userDateRegistered is null
GROUP BY a.siteID

[mga_ka_php]

still doesn't work.

 

if i remove the WHERE

 

result would be

Site        | New Members

site.com  | 50

site2.com | 10

site3.com | 25

 

result is good, it gets the total number of new members, all time

 

but if i add the WHERE, it will not show all the sites, it will only show the sites with new members

 

Site        | New Members

site.com  | 50

site3.com | 25

[mga_ka_php]

thinking about this sql statement

SELECT a.siteDomain 'Site'
FROM sites a
WHERE
(
SELECT COUNT(b.userID)
FROM users b
WHERE
b.userDateRegistered between '1231513284' and '1231553482'
)

 

can i move the COUNT(b.userID) to SELECT a.siteDomain 'Site'?

[fenway]

I don't understand what's wrong anymore.

[mga_ka_php]

figured it out.thanks.

 

what i did was

SELECT siteDomain 'Site',
(
   SELECT COUNT(userID)
   FROM users
   WHERE (userDateRegistered BETWEEN '1231733663' AND '1231910698') AND users.siteID=sites.siteID
) 'New Members',
FROM sites