SQL Where clause

Question

My application initially had a query similar to this one:-

SELECT column_name from PERSON
WHERE name in (list);

where list is comma separated list.

But, now the requirement has changed and i have to query the Persons table with name and age given. I have the nameAgeList.

Initially, i thought a query similar to this would work (Create nameList and ageList from nameAgeList)

SELECT column_name from Person 
WHERE name in (nameList) 
AND age in (ageList)

But after carefully thinking, this seems to be a wrong query.

Please let me know how should I proceed ahead with this query.

What is `nameAgeList`? Why is this the "wrong query"? Does it give incorrect results? Do you get an error? Did your server explode? — Adam Robinson, Aug 21 '11 at 01:08
Is this perhaps homework or some such? What database are you using, or are you only allowed standard sql? — jswolf19, Aug 21 '11 at 01:09
1) What platform? 2) What form is the `nameAgeList`? 3) If it's a string, why? 4) Are the names actually related to ages? — Aaronaught, Aug 21 '11 at 01:36
I am using Java and Oracle is the database. nameAgelist is a list of input objects where input consists of a string and int, which correspond to name and age. This is an input given to me. Before the change in the requirement, i had nameList which was a list of strings corresponding to the name, but now its a list of objects. — Lex, Aug 21 '11 at 02:33

score 5 · Answer 1 · answered Aug 21 '11 at 02:44

5

Under Oracle, you can do this:

SELECT * FROM Person
WHERE
    (name, age) IN (
        ('name1', age1),
        ('name2', age2)
        -- Etc...
    )

You can have up to 1000 tuples in this list.

answered Aug 21 '11 at 02:44

Branko Dimitrijevic

50,809
10
93
167

score 2 · Answer 2 · answered Aug 21 '11 at 01:58

2

One option is to create a temporary table (or if SQL Server, a table variable), place your names and ages in this table, and then simply join to it:

SELECT column_name from Person p
INNER JOIN myTempTable t ON t.Name = p.Name AND t.age = p.age

answered Aug 21 '11 at 01:58

Mitch Wheat

295,962
43
465
541

score 0 · Answer 3 · answered Aug 21 '11 at 01:26

It's not pretty, and this one only works when you can generate your statement in code:

SELECT column from Person
    WHERE 1=1
      AND (   ( name = name1 and age = age1 )
           OR ( name = name2 and age = age2 )
           OR ( name = name3 and age = age3 )
           OR ( name = name4 and age = age4 )
           OR ( name = name5 and age = age5 )
           ... et cetera 
          )

Now, if you could put those lists into tables you could do alot better than this. Is there any way you can get those lists into the database? I assume that you really need some Person table that holds name and age for each individual.

yeah, that's horrible, don't do that! – Mitch Wheat Aug 21 '11 at 01:56 — Mitch Wheat, Aug 21 '11 at 01:56

SQL Where clause

3 Answers3