Re: SQL Syntax Queestion

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:

SELECT * FROM Households
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 Tue Jun 09 1998 - 22:43:10 CEST