I'm hoping someone here can help me resolve this problem.
I think I'm almost there I'm just missing something that I've overlooked.
What I'm trying to do is create one custom MySQL table out of 3 existing MySQL Tables.
This is what I've come up with...
SELECT proj.p_id, proj.p_name, proj.p_status, COUNT(domain) AS domain_count, SUM(IF(pt_status = 3 , 1, 0 )) AS published_posts, SUM(IF(pt_status = 2 , 1, 0 )) AS approved_posts, SUM(IF(pt_status = 1 , 1, 0 )) AS queued_posts, SUM(IF(pt_status < 1 , 1, 0 )) AS declined_posts FROM wp_cglg_projects proj LEFT JOIN wp_cglg_domains d ON proj.p_id = d.project_id LEFT JOIN wp_cglg_posts pt ON d.d_id = pt.domain_id GROUP BY proj.p_id;
The statement above produces the table I want except the domain count in my first row is incorrect. It should
be 2 not 3.
1. Below is picture of my Custom Table...
Here is a picture of my existing 3 tables that I'm referencing to create my custom table.
Mysql Dump File
Finally if you wish to take a look here is my mysql dump file you can download and dump into your test db.
Thanks for any help in advance.
Edited by sbsmith, 04 December 2011 - 10:43 AM.