zskull88
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'
ASKER
same thing still has
r i
1150 1150
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
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
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(*) 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'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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`
ASKER
Sorry it took so long but this work great thanks
try this one
Open in new window