how to combine the result of one Mysql (sub)query that returns multiple rows and use that for another query
I 开发者_高级运维have two tables, one that stores serial number uut_build, and another for measurements that belong to the serial numbers measure_list, I'm trying to get the count of records in measure_list that belongs to a unique serial number.
Query #1select id, uut_sn from uut_build where uut_sn like 'A%';
Query #2
select count(*) from measure_list as m WHERE m.uut_id = [result from query #1]
Is it possible to do this with just one query statement?
Thanks in advance!
Use:
SELECT ub.uut_sn,
COALESCE(COUNT(ml.*), 0) AS cnt
FROM UUT_BUILD ub
LEFT JOIN MEASURE_LIST ml ON ml.uut_id = ub.uut_sn
WHERE ub.uut_sn LIKE 'A%'
GROUP BY ub.uut_sn
It's a little unclear what columns link the two tables...
This will return all serial numbers - if they don't have a record in MEASURE_LIST
, the cnt value will be zero. If you only want a list of records that have records in MEASURE_LIST
, remove "LEFT" from the query I provided.
精彩评论