At 10:13 AM 12/2/2009, you wrote:
Tom Worster wrote:
> how about using LEFT JOIN:
>
> SELECT ...
> FROM listings a
> LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber
This gives me a result set of 456,567 lines. I'm looking for a result
set of 60-70 lines. (That's how many part numbers we have.)
> and for speed, does Transactions.PartNumber have an index?
Done... I didn't think that you could do that for columns where duplicates
were allowed.
mos wrote:
> 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.
SELECT a.PartNumber, count(1)
FROM listings a
LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber
group by a.PartNumber
This gives results that almost look right (66 lines), however there are
some unrealistically high numbers. The transactions totals for each part
number, when added together, come to 545,325. This is obviously wrong,
because there are only 7000 transactions.
The Counts are likely wrong because you are using Count(1). Don't forget
that the left join will join to the second table even if the b row does not
exist (returns all NULL's). Therefore you need to count only rows returned
from the 2nd table, correct? Then do something like
Sum(If(b.part=NULL,0,1)) so if there is no b row it sums 0, otherwise is
sums 1.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
