mapleleaf Posted February 10, 2012 Share Posted February 10, 2012 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 Quote Link to comment Share on other sites More sharing options...
mapleleaf Posted February 10, 2012 Author Share Posted February 10, 2012 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 Quote Link to comment Share on other sites More sharing options...
mapleleaf Posted February 10, 2012 Author Share Posted February 10, 2012 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.