radar Posted August 20, 2010 Share Posted August 20, 2010 Alright so I am joining 3 tables, 2 tables are only brought in for the count. it counts just fine, but i have 7 records in the database but only 3 of them are being displayed... $sql = "SELECT SQL_CALC_FOUND_ROWS cust.id, cust.f_name, cust.l_name, cust.email, cust.password, cust.status, COUNT( o.id ) AS orders_cnt, COUNT( d.id ) AS deals_cnt FROM customers AS cust LEFT JOIN orders AS o ON o.c_id = cust.id LEFT JOIN deals AS d ON d.c_id = cust.id GROUP BY o.c_id, d.c_id ORDER BY cust.l_name"; and the output in array form from this join is: Array ( [0] => Array ( [id] => 2 [f_name] => [l_name] => => tech@mysite.com [password] => [status] => 1 [orders_cnt] => 0 [deals_cnt] => 0 ) [1] => Array ( [id] => 1 [f_name] => Justin [l_name] => Brown => justin@mydomain.com [password] => li26tuvs [status] => 1 [orders_cnt] => 0 [deals_cnt] => 1 ) [2] => Array ( [id] => 12 [f_name] => k [l_name] => k => keith@mysite.net [password] => y1hiq6oc [status] => 1 [orders_cnt] => 0 [deals_cnt] => 2 ) ) so with that join is there any reason only 3 of them would be displayed? even running is through phpMyAdmin retrieves only 3 of them as well. Quote Link to comment Share on other sites More sharing options...
radar Posted August 20, 2010 Author Share Posted August 20, 2010 alright i figured this out... i first tried just a regular select * from table this of course brought all of them... i then tried lowing it down to 1 left join, didnt quite work as planned.. after about 10 different tries with all the same result i decided to try to add cust.id into my group by line, so now i have GROUP BY c.c_id, d.c_id, cust.id and it worked perfectly. i now get all of the results expected. 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.