使用单个查询从表A中的值计算表B上的结果数

Question

Is it possible to count the number of results matched in table B from values drawn from table A in the same query?

For example -

tableA
--------------
color  | type
-------|------
blue   | car
red    | truck
orange | car
green  | car

-

tableB
--------------
color  | sku
-------|------
blue   | 1
red    | 2
orange | 3
green  | 4
orange | 5
green  | 6

Question - How many rows in tableB are related to type = car? Result should return 5.

Using two queries and some PHP this is pretty straightforward, I was just curious if this is possible with a single MySQL query, and if it would be more efficient.

Answer 1

Try the below query

SELECT count(*) AS count 
FROM tableB b, tableA a 
WHERE b.color = a.color 
AND type = 'car';