Pierpaolo Piccoli Pierpaolo Piccoli - 4 months ago 8
SQL Question

Quey sql : How check existence of multiple rows with one query

I have this table : MyTable

MYTABLE
PROG VALUE
-------------
1 aaaaa
1 bbbbb
2 ccccc
4 ddddd
4 eeeee


now i'm checking the existence of a tuple with a certain id with a query like

SELECT COUNT(1) AS IT_EXISTS
FROM MyTable
WHERE ROWNUM = 1 AND PROG = {aProg}


for example i obtain with aProg = 1 :

IT_EXISTS
---------
1


i obtain with aProg = 3 :

IT_EXISTS
---------
0


the problem is that i must do multiple queries, one for every value of PROG to check.

What i want is something that with a query like

SELECT PROG, ??? AS IT_EXISTS
FROM MyTable
WHERE PROG IN {1, 2,3, 4, 5} AND {some other condition}


i can obtain something like

PROG IT_EXISTS
------------------
1 1
2 1
3 0
4 1
5 0


The database is Oracle...

Hope i'm clear

regards

Paolo

Answer

Take a step back and ask yourself this: Do you really need to return the rows that don't exist to solve your problem? I suspect the answer is no. Your application logic can determine that records were not returned which will allow you to simplify your query.

SELECT PROG
FROM MyTable
WHERE PROG IN (1, 2, 3, 4, 5)

If you get a row back for a given PROG value, it exists. If not, it doesn't exist.

Update:

In your comment in the question above, you stated:

the prog values are from others tables. The table of the question has only a subset of the all prog values

This suggests to me that a simple left outer join could do the trick. Assuming your other table with the PROG values you're interested in is called MyOtherTable, something like this should work:

SELECT a.PROG,
CASE WHEN b.PROG IS NOT NULL THEN 1 ELSE 0 END AS IT_EXISTS
FROM MyOtherTable AS a
LEFT OUTER JOIN MyTable AS b ON b.PROG = a.PROG

A WHERE clause could be tacked on to the end if you need to do some further filtering.