Re: SQL Syntax Queestion

cool is:

SELECT * FROM Households A
WHERE EXISTS (SELECT NULL

                                 FROM      AnotherTable B
                                 WHERE  B.LastName = A.LastName
                                 AND         B.FirstName = A.FirstName)

but then you'll get all records that exists once or more in AnotherTable if you want to get only the ones that existsting more than twice;

SELECT * FROM Households A

WHERE 1 >          (SELECT  count(*)
                                 FROM      AnotherTable B
                                 WHERE  B.LastName = A.LastName
                                 AND         B.FirstName = A.FirstName)

but this doesn't perform very well.

mars_at_writeme.com

Kevin P. Fleming heeft geschreven in bericht <357D9E5D.F7E8F5F3_at_access-laserpress.com>...
>Yes, you'll have to combine the fields together somehow so that they
>appear as a single "object" to the SQL processor. If the fields are the
>same data types and sizes, a simple string concatenation will probably
>suffice.
>
>Note that this will cause the query to not be able to use any indexes,
>though, so this could be a problem for you. If the Households table has
>a primary key called ID, here's another possibility for you:
>
>
>WHERE ID IN (
> SELECT ID FROM Households A, AnotherTable B
> WHERE A.LastName=B.LastName AND
> A.FirstName=B.FirstName
>)
>
>This would use indexes on LastName and FirstName and ID, and should be
>pretty fast.
>
>Roger Loeb wrote:
>>
>> I am trying to handle some duplication logic in SQL and I don't
understand
>> the syntax for a compound key.
>>
>> What I want to say is something like --
>>
>> Select * from Households where Households.LastName, Households.FirstName
IN
>> (Select LastName, FirstName from AnotherTable by LastName, FirstName
having
>> count (*) > 1);
>>
>> In other words, for any lastname+firstname combination that exists more
than
>> once in AnotherTable, I want to select the record for the same
>> lastname+firstname combination from the "Household" table. However, this
>> doesn't seem to do what I expect.
>>
>> Do I need to do something like concatenate the two fields together in
both
>> parts of the statement?
>>
>> TIA,
>>
>> Rog
>>
>> --
>> roger_at__delete_this_to_reply_.martech.com
Received on Wed Jun 10 1998 - 11:53:37 CEST