[teterin]

Hello!

A unique key on this particular table would be a combination of two different columns. Suppose, my program has a set of pairs of values -- how can it get rows describing each pair only?

For example, if the key is a combination of first and last names, and I'm interested in 3 different people... I thought, something like this would work:

SELECT * FROM PEOPLE WHERE (FirstName, LastName) in ((Joe, Foo), (Jim, Bar), (Woof, Meow))

This causes a syntax error, however... I can, of course, use string concatenation to produce a single-string key, but that's not very elegant and a bit harder for both the server and the client to do:

SELECT * FROM PEOPLE WHERE FirstName+LastName in ("JoeFoo", "JimBar", "WoofMeow))

In fact, if the table already has a combined index on both fields, the above query will not use it

What's the right way to do this? Thanks!

[pdreyer]

If performance is important, use a temp table to store the array of names to search.

select * into #testdata from (
select 'Joe' union all 
select 'Jim' union all 
select 'Woof' 
)a(FirstName),(
select 'Foo' union all 
select 'Bar' union all
select 'Meow' 
)b(LastName)
create index ix1 on #testdata(FirstName,LastName)
------
select * into #namearray from (
select 'Joe','Foo' union all 
select 'Jim','Bar' union all 
select 'Woof','Meow')namearray(FirstName,LastName)
------
select a.* 
from #testdata a 
join #namearray b
 on  a.FirstName=b.FirstName
 and a.LastName =b.LastName
------
drop table #testdata 
drop table #namearray

[teterin]

Ouch... That's messy... I think, I'd rather handle this logic on the client-side, then

That said, maybe, I can construct an (arbitrarily long) line like:

where
    (FirstName='Joe' and LastName='Foo') or
    (FirstName='Jim' and LastName='Bar') or
    ....
    (FirstName='Woof' and LastName='Meow')

Thanks!

[pdreyer]

Don't store data in your code

What is the significance of these names
If they are e.g. administrators keep a flag on the table to flag them as such
or store them in an administrators table that you can join to.

Then if the administrators change, all you have to do is update a table instead of your code.

[teterin]

Just because a program wishes to inquire about certain names only, does not mean, then names are "stored in the code"

Database is not the only source of data.

The names may come from user input, from flat file, or from a completely different database.

This is why the in (.....) construct exists in the first place, I believe. Unfortunately, the designers did not think of multiple dimensions, when it was created

[trvishi]

Well basically YOU CANT do a select with IN clause with multiple columns. I would be surprised OTHER dbmses do it, but Sybase cant as far as I know... So, instead of beating up the ISQL utility (you are not going to get anywhere), try to workaround it

Its almost saying as if, if you write a piece of code in Java, it MUST work in C, C++, or if you write in Oracle and it must work in SQL Server and Sybase