Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Unanswered: where (set of columns) in ((set1), (set2), ... (setN)) ?

    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:
    Code:
    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:

    Code:
    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!
    Last edited by teterin; 11-19-07 at 18:37. Reason: mention index-defeating quality of the second query
    If you ever back up Sybase, you want this backup-server plugin.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    If performance is important, use a temp table to store the array of names to search.
    Code:
    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

  3. #3
    Join Date
    Apr 2003
    Posts
    64
    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:
    Code:
    where
        (FirstName='Joe' and LastName='Foo') or
        (FirstName='Jim' and LastName='Bar') or
        ....
        (FirstName='Woof' and LastName='Meow')
    Thanks!
    If you ever back up Sybase, you want this backup-server plugin.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Apr 2003
    Posts
    64
    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
    If you ever back up Sybase, you want this backup-server plugin.

  6. #6
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    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
    Last edited by trvishi; 11-26-07 at 19:32.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •