At 01:21 PM 12/1/2009, David Shere wrote:
select distinct a.PartNumber as 'Part Number', (
select count(1)
from Transactions b
where b.PartNumber = a.PartNumber) as 'No. Sold'
from listings a
order by a.PartNumber
It currently takes 30 seconds to run. Transactions has 1200 records and
listings has 7000.
Multiple listings can have the same part number, as can transactions. We'd
like to know how many transactions there are for each part number,
including those part numbers for which there are listings but no
transactions. Given the "and zero transactions" requirement, I can't
figure out how to do this query with a join.
Do the Left Join as the other person said and also replace the "Distinct"
"Order By" with "Group by PartNumber" and you won't need the sort.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
