>>>>> "Martin" == Martin Ramsch <m.ramsch@stripped> writes:
Martin> On Fri, 1999-09-24 14:06:25 +0100, Philip Brown wrote:
>> I want to issue the following query (syntax simplified):
>>
>> SELECT X.* FROM X LEFT JOIN Y GROUP BY X.ID HAVING COUNT(Y.NNN)>1
>>
>> In other words, select records from X which have 2 or more linked
>> records in Y.
Martin> You don't need a LEFT JOIN here, because COUNT(Y.NNN) won't count NULL
Martin> columns anyway!
Martin> So
Martin> SELECT X.* FROM X, Y WHERE ... GROUP BY X.ID HAVING COUNT(Y.NNN)>1
Martin> should also work and be much faster.
Hi!
Try doing:
SELECT SQL_SMALL_RESULT X.* FROM X LEFT JOIN Y GROUP BY X.ID HAVING COUNT(Y.NNN)>1
If there is only a small different X.ID values, the above should work
and it will only need a temporary row for each different X.ID.
You can also try MySQL 3.23 that can handle GROUP BY queries much
better...
Regards,
Monty
