Re: SQL Syntax Queestion
From: Matt Brennan <mbrennan_at_gers.antispam.com>
Date: Tue, 09 Jun 1998 21:23:51 GMT
Message-ID: <01bd93ec$e0a7cc20$049a0580_at_mcb>
Date: Tue, 09 Jun 1998 21:23:51 GMT
Message-ID: <01bd93ec$e0a7cc20$049a0580_at_mcb>
How about this...?:
Select * from
Households
where Households.LastName||Households.FirstName IN
(Select LastName||FirstName from AnotherTable
having count (LastName||FirstName) > 1
group by LastName||FirstName);
You also might have to use NVL to force a constant for either or both columns if they can be null.
-- Matt Brennan SQL*Tools Specialist GERS Retail Systems 9725-C Scranton Road San Diego, California 92121 1-800-854-2263 mbrennan_at_gers.com (Original email address is spam-blocked.) Roger Loeb <rloeb_at_martech.com> wrote in article <6ljpof$97v$1_at_news1.rmi.net>...Received on Tue Jun 09 1998 - 23:23:51 CEST
> 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
>
>
>