CODEWITHSUNDEEP
sqloracle-database
Arham
Arham

Reputation: 2102

Select statement having multiple conditions over multiple columns

A part of my table is as follows,

Key|Value
---------
 A | V1
 B | V2
 C | V3

I know the following query would definitely return no result, but then what is the correct query of achieving a positive result, i.e. '1'.

I mean what query should I apply that checks that A has a value as V1 and B has a value as V2 and then returns me '1'. The new query should fail to return anything if either A or B has different results.

Assume the table as a Map of key/value pairs. Only difference here is it's not a Java map but a table in Oracle Db. So, how to achieve a result that satisfies both the key/value pairs.

select 1 from myTable 
where (key = 'A' and value = 'V1') 
  AND (key = 'B' and value = 'V2');

Let me know if the design of the table itself needs some change.

Upvotes: 0

Views: 3244

Answers (3)

SWeko
SWeko

Reputation: 30912

Using my powers of psychic debugging:

You want to have a value of 1 if:

  1. There is a row with Key="A" and value="V1" AND
  2. There is another row with Key="B" and value="V2"

To get a row like the first you need:

select 1 from myTable where key = 'A' and value = 'V1'

To get a row like the second you need

select 1 from myTable where key = 'B' and value = 'V2'

Now you need to make sure that both those rows exist.

It's not a simple as it sounds, since SQL checks all where conditions on a single row, so a statement like:

select 1 from myTable where key = 'A' and key = 'B'

is nonsensical, because it requires the key column to have two distinct values simultaneously.

One (inefficient) solution is to join the table to itself

select 1 
from mytable t1
  cross join mytable t2
where t1.Key = 'A' and t1.Value='V1'
  and t2.Key = 'B' and t2.Value='V2'

This will make a Cartesian product of the tables, joining each row with each other row. It will generate 

t1.Key|t1.Value|t2.Key|t2.Value
-------------------------------
 A    | V1     |  A    |  V1
 B    | V2     |  A    |  V1
 C    | V3     |  A    |  V1
 A    | V1     |  B    |  V2   <-- the row you need
 B    | V2     |  B    |  V2   
 C    | V3     |  B    |  V2   
 A    | V1     |  C    |  V3   
 B    | V2     |  C    |  V3   
 C    | V3     |  C    |  V3

and will enable you to check two rows of the original table at the same time.

Note that this will generate a table of count^2 rows, so DO NOT USE IT if the table has more than a few rows, or if you need to check more than two rows simultaneously.

Upvotes: 1

Noel
Noel

Reputation: 10525

I think this is what you want.

select 1 from dual
 where exists(
              select 1
                from mytable
               where key = 'A' and value = 'V1')
   and exists(
              select 1
                from mytable
               where key = 'B' and value = 'V2')

Upvotes: 2

schurik
schurik

Reputation: 7928

if you only want to check if there are rows that fulfill certain conditions you can use following construct

select count(*) from dual
where exists (
               select 1  
               from myTable 
               where (key = 'A' and value = 'V1')                       
              )
 AND exists (
               select 1  
               from myTable 
               where (key = 'B' and value = 'V2')                       
              );

Upvotes: 0

Related Questions