Get count on a second table if id matches id in results of first table

[mapleleaf]

I have 2 tables.

One of emails

Second email lists

Every email in the first table has a list id. Every list has a user_id

What would the sql be to get all the lists of a user with the count of the emails in the email table with the list_id?

 

Something like:

Select list_name, (SELECT count(email) from emails) as EmailCount 
from email_lists, emails 
where email_lists.list_owner_id = $user_id AND emails.list_id = email_lists.id

This returns all the emails without checking the count correctly. I am missing something. a subquery I think

 

 

[mapleleaf]

I think the update option's time expired.

MYSQL 5.1.37

Just to clarify I get the list name back and a count of 6 for the email count which is all the emails in the emails table.

I am trying to get the count of emails with each list id associated.

Emails

Field 	Type 	Null 	Key 	Default 	Extra
id 	int(12) 	NO 	PRI 	NULL 	auto_increment
email 	varchar(100) 	NO 	  	NULL 	 
list_id 	int(10) 	NO 	  	NULL 	 
added_by 	int(12) 	NO 	  	NULL 	 
date_added 	int(10) 	NO 	  	NULL 	 

 

Email_lists

id 	int(11) 	NO 	PRI 	NULL 	auto_increment
list_owner_id 	int(11) 	YES 	MUL 	NULL 	 
list_name 	varchar(45) 	YES 	  	NULL 	 
create_date 	int(10) 	NO 	  	NULL 	 
last_update 	int(10) 	NO 	  	NULL 	 
creator_id 	int(10) 	NO 	  	NULL 	 
event_ids 	varchar(255) 	NO 	  	NULL 	 

[mapleleaf]

Select list_name, count(email) as EmailCount from email_lists, emails where email_lists.list_owner_id = $user_id AND emails.list_id = email_lists.id group by email_lists.id

Figured it out