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.
Try the below query
SELECT count(*) AS count
FROM tableB b, tableA a
WHERE b.color = a.color
AND type = 'car';
Join your tables on the colors that correspond to your type :
SELECT COUNT(*) FROM tableB JOIN tableA using (color) WHERE type = 'car';
I would try something like:
SELECT COUNT(*)
FROM tableA, tableB
WHERE tableA.color=tableB.color
AND tableA.type='car';