Re: SQL Syntax Queestion

From: Mars <mars_at_writeme.com>
Date: Wed, 10 Jun 1998 20:40:15 +0200
Message-ID: <6lmkrg$aoj$1_at_cadmium.aware.nl>


you forgot the jion of anothertable;

Select * from
Households
where Households.LastName||Households.FirstName IN (Select LastName||FirstName from AnotherTable where LastName||FirstName = Households.LastName||Households.FirstName
having count (LastName||FirstName) > 1
group by LastName||FirstName);

how about...

Select B.LastName,B.FirstName
from Households B
, anothertable A
where A.LastName =B.LastName
and A.FirstName = B.FirstName
having count (*) > 1
group by B.LastName,B.FirstName;

Matt Brennan heeft geschreven in bericht <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>...
>> 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 - 20:40:15 CEST

Original text of this message