I tried the suggestion:
>SELECT Count(P.Ref) as Counter, C.Ref, C.Surname, C.Forename >FROM Clients
C
>JOIN Purchases P ON C.Ref=P.Ref
>WHERE Counter=0
>GROUP BY C.Ref
but I get the message "Unknown column `counter` in `where clause`
Any other ideas out there?
>-- Original Message --
>From: "Petr Vileta" <petr@stripped>
>To: <win32@stripped>
>Subject: Re: Bad query structure...
>Date: Tue, 20 Jul 2004 16:21:36 +0200
>
>
>> I am trying to identify items with no records in a sub-set, such as a
>customer
>> with no purchases. I have tried several different queries but none
>provide
>> the correct result set, eq:
>>
>> SELECT Count(P.Ref)=0, C.Ref, C.Surname, C.Forename FROM Clients C JOIN
>> Purchases P ON C.Ref=P.Ref
>
>SELECT Count(P.Ref) as Counter, C.Ref, C.Surname, C.Forename FROM Clients
>C
>JOIN
>Purchases P ON C.Ref=P.Ref WHERE Counter=0 GROUP BY C.Ref
>
>
>Petr Vileta, Czech republic
>
>
>--
>MySQL Windows Mailing List
>For list archives: http://lists.mysql.com/win32
>To unsubscribe: http://lists.mysql.com/win32?unsub=1
>
__________________________________________________
Broadband from an unbeatable £15.99!
http://www.tiscali.co.uk/products/broadband/home.html?code=SM-NL-11AM
