search for multiple columns with "in" keyword? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

search for multiple columns with "in" keyword?

Author

Topic

mchilders
Starting Member

5 Posts

Posted - 2006-04-13 : 10:10:23

How do you accomplish something like:

select * from invoiceaccounts
where invoicedate, carrier, account in (select invoicedate, carrier, account from invoice)

I'm refering specifically to the use of the "in" keyword in looking for a combination of columns. I need this because I'm using a composite key containing those four columns in the invoice table.
Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-13 : 10:30:28


select * from invoiceaccounts ia
Inner join invoice i on 
ia.invoicedate = i.invoicedate and ia.carrier = i.carrier and ia.account = i.account

Srinika

Kristen
Test

22859 Posts

Posted - 2006-04-13 : 11:03:19

That may give you multiple hits on the InvoiceAccounts table (assumign multiple Invoice records per InvoiceAccount record), if so you will need a DISTINCT on the results, or a nested sub-select that uses a DISTINCT on the INVOICE table

You want to get shot of the SELECT * and use named columns, but in the absence of that it would be:

SELECT DISTINCT ia.* FROM ...

Kristen

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-13 : 11:04:34

Or this (which might perform slightly better):

select * from invoiceaccounts ia
where exists (select * from invoice i where ia.invoicedate = i.invoicedate
              and ia.carrier = i.carrier and ia.account = i.account)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-13 : 11:21:58

Kristen,
I admit my mistake

RyanRandall,
I learned that

Thanks both of you.
Because of people like you this site is a source of knowledge.

Srinika

mchilders
Starting Member

5 Posts

Posted - 2006-04-13 : 12:00:24

I see. Thanks a lot!
I've just not seen "exists" used in a query before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources