On 12/1/09 2:21 PM, "David Shere" <dsh...@steelerubber.com> 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. how about using LEFT JOIN: SELECT ... FROM listings a LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber ... wouldn't that tabulate also the unsold parts? and for speed, does Transactions.PartNumber have an index? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
