Some Counting Join Questions

[radar]

Okay so in my database I have 3 tables.

 

One named Customers, One named Orders and one Named Deals

 

I need to select * from Customers and count(id) from Orders where d_id == cust.id and count(id) from deals where d_id == cust.id

 

But I have no clue how to query that result.  any help is appreciated.

 

Only issue to this, is I have modified a smarty paginate class to work with my system so I am using Smarty Paginate to do pagination and so I would still need to be able to run the limit modifier, as well as SQL_CAL_FOUND_ROWS from the customers table.

[Psycho]

Not tested:

SELECT cust.field1, cust.field2, cust.field3,
       COUNT(Orders.id) as order_Count,
       COUNT(Deals.id) as deals_count
FROM Customers as cust,
LEFT JOIN Orders
  ON Orders.d_id = cust.id
LEFT JOIN Deals  as d
  ON Deals.d_id = cust.id
GROUP BY Orders.d_id, Deals.d_id

[radar]

Okay now with that how would I add in the SQL_CALC_FOUND_ROWS after the select, as well as the Limit x,x onto it?

 

my current query (robbed from my employees code), is like:

 

$sql = sprintf("SELECT SQL_CALC_FOUND_ROWS * FROM customers LIMIT %d,%d",
            					SmartyPaginate::getCurrentIndex(), SmartyPaginate::getLimit());

 

though I can get rid of the sprintf easily, though the SQL_CALC_FOUND_ROWS is essential for the pagination, as is the limit.

[radar]

Had to modify the original query you gave me a little bit but I have it working.

 

 

$sql = "SELECT SQL_CALC_FOUND_ROWS cust.id, cust.name, cust.email, cust.password, cust.optin, cust.status, o.c_id, d.d_id, 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.d_id = cust.id

GROUP BY o.c_id, d.d_id LIMIT".SmartyPaginate::getCurrentIndex().", ".SmartyPaginate::getLimit();

 

 

havent done the trials through PHP yet, but it works in phpmyadmin, which would mean it would theoretically work elsewhere too.