Link to home
Start Free TrialLog in
Avatar of zskull88
zskull88Flag for United States of America

asked on

MySQL Count 2 tables in 1 query with a where clause

There's only 1 in the i and 0 in the r but its returning all the results i have try if statements joins and unions. all with the same result. and nothing equals the same
select count(o.orderNum) as `r`, count(s.orderID) as `i` from `orders` o, `shoppingCart` s where s.orderID = '1166233652' or o.orderNum = '1166233652'

Open in new window

Avatar of nasirbest
nasirbest
Flag of Pakistan image

you need a little trick! with a dummy column you can satisfy GROUP BY

try this one
SELECT 1 AS `dummy`,
       COUNT(o.orderNum) AS `r`, 
       COUNT(s.orderID) AS `i` 
FROM `orders` o, `shoppingCart` s 
WHERE s.orderID = '1166233652' OR o.orderNum = '1166233652'
GROUP BY 1

Open in new window

Avatar of zskull88

ASKER

same thing still has
 r       i
1150       1150
there is no criteria defined to prevent cartesian product.

looking again in your query I found that you are searching same order number in both tables. so you can use following


SELECT 1 AS `dummy`,
       COUNT(o.orderNum) AS `r`, 
       COUNT(s.orderID) AS `i` 
FROM `orders` o JION `shoppingCart` s 
     ON o.orderNum = s.orderID
WHERE o.orderNum = '1166233652'
GROUP BY 1

Open in new window

please try this.

select count(*) as 'r', @cnt as 'i' from orders where (@cnt) in (select @cnt:=count(*) from shoppingCart where orderID  =1166233652) and orderNum  = 1166233652;
select count(o.orderNum) as `r`, count(s.orderID) as `i` from `orders` o, `shoppingCart` s group by o.orderNum, s.orderID having s.orderID = '1166233652' or o.orderNum = '1166233652'
sorry it should be 'and' only:

select count(o.orderNum) as `r`, count(s.orderID) as `i` from `orders` o, `shoppingCart` s where s.orderID = '1166233652' and o.orderNum = '1166233652'
ASKER CERTIFIED SOLUTION
Avatar of tikusbalap
tikusbalap
Flag of Indonesia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sharath S
I think you are looking for this
SELECT (SELECT COUNT(o.orderNum) 
        FROM   `orders` o 
        WHERE  o.orderNum = '1166233652') AS `r`, 
       (SELECT COUNT(s.orderID) 
        FROM   `shoppingCart` s 
        WHERE  s.orderID = '1166233652') AS `i`

Open in new window

Sorry it took so long but this work great thanks