Two Table Query Count - HELP!?!?!

[phousley]

I'm trying to accomplish something that just seems like it should work. I'm obviously just too close to the problem...

I have two tables the first table contains a list of sites:

siteid, title, url, active
1,Bob,www.bob.com,1
2,Pete,www.pete.com,0

The second table contains a list of users of a site:

email, sitenumber
[e-mail],1
[e-mail],2
[e-mail],1

What I am attempting to do is count the number of users in table 2 and check the site the user belongs to is active (1) in table 1, the ultimate goal is to show a listing of the most popular active sites... HELP!?! Thanks!

[phousley]

Okay so I tried this, but the query never comes back... There are almost 300,000 records in the memberships data table, but I can get a top count from the table in seconds, so I don't think the problem is the size of the table.

$sql="SELECT s.*,m.username,COUNT(s.username) AS num

FROM memberships m LEFT JOIN sites s

ON m.sitenumber=s.number

WHERE s.type='1' GROUP BY m.sitenumber";

[tomhath]

The query you posted can't possible work, it contains several glaring errors.

Only select columns that you are grouping by, maybe something like this:

$sql="SELECT s.title, COUNT(*) AS num
FROM memberships m
INNER JOIN sites s
ON m.sitenumber=s.siteid
WHERE s.active='1'
GROUP BY s.title";