CODEWITHSUNDEEP
sqloracle-database
Pierpaolo Piccoli
Pierpaolo Piccoli

Reputation: 145

SQL query : how to check existence of multiple rows with one query

I have this table 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 get 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 get 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

Upvotes: 11

Views: 26180

Answers (4)

sstan
sstan

Reputation: 36523

It's still not very clear where you get the prog values to check. But if you can read them from a table, and assuming that the table doesn't contain duplicate prog values, this is the query I would use:

select a.prog, case when b.prog is null then 0 else 1 end as it_exists
  from prog_values_to_check a
  left join prog_values_to_check b
    on a.prog = b.prog
   and exists (select null
                 from MyTable t
                where t.prog = b.prog)

If you do need to hard code the values, you can do it rather simply by taking advantage of the SYS.DBMS_DEBUG_VC2COLL function, which allows you to convert a comma-delimited list of values into rows.

with prog_values_to_check(prog) as (
  select to_number(column_value) as prog
    from table(SYS.DBMS_DEBUG_VC2COLL(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) -- type your values here
)
select a.prog, case when b.prog is null then 0 else 1 end as it_exists
  from prog_values_to_check a
  left join prog_values_to_check b
    on a.prog = b.prog
   and exists (select null
                 from MyTable t
                where t.prog = b.prog)

Note: The above queries take into account that the MyTable table may have multiple rows with the same prog value, but that you only want one row in the result. I make this assumption based the WHERE ROWNUM = 1 condition in your question.

Upvotes: 0

Asaph
Asaph

Reputation: 162851

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.

Upvotes: 11

Matthew G
Matthew G

Reputation: 196

One solution is to use (arguably abuse) a hierarchical query to create an arbitrarily long list of numbers (in my example, I've set the largest number to max(PROG), but you could hardcode this if you knew the top range you were looking for). Then select from that list and use EXISTS to check if it exists in MYTABLE.

select
      PROG
    , case when exists (select 1 from MYTABLE where PROG = A.PROG) then 1 else 0 end IT_EXISTS
from (
    select level PROG
    from dual
    connect by level <= (select max(PROG) from MYTABLE) --Or hardcode, if you have a max range in mind
) A
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I would recommend something like this. If at most one row can match a prog in your table:

select p.prog,
       (case when t.prog is null then 0 else 1 end) as it_exists
from (select 1 as prog from dual union all
      select 2 as prog from dual union all
      select 3 as prog from dual union all
      select 4 as prog from dual union all
      select 5 as prog from dual
     ) p left join
     mytable t
     on p.prog = t.prog and <some conditions>;

If more than one row could match, you'll want to use aggregation to avoid duplicates:

select p.prog,
       max(case when t.prog is null then 0 else 1 end) as it_exists
from (select 1 as prog from dual union all
      select 2 as prog from dual union all
      select 3 as prog from dual union all
      select 4 as prog from dual union all
      select 5 as prog from dual
     ) p left join
     mytable t
     on p.prog = t.prog and <some conditions>
group by p.prog
order by p.prog;

Upvotes: 0

Related Questions