Query regarding formation of SQL query

Question

I have a huge table containing data of the form given below:

Id Col1  Col2      Col3
------------------------
a Fruit1 vitaminA vitaminB 
b a      price       "30"     

Now I want to retrieve all fruits containing vitaminA and vitaminB having price less than 30 in SQL. here 'a' is the id which is given to Fruit1. Fruit1 contains VitaminA and vitaminB. Now, the next row indicates that id 'a' has price 30.

My intent is to retrieve all fruits having vitaminA and vitaminB and having price less than 30. Is there a way in SQL by which I may answer this query?

Answer 1

You will have to use a self-join on your table to get the result.

select t1.id
from yourtable t1
inner join yourtable t2
  on t1.id = t2.col1
where 
(
  t1.col2 = 'vitaminA' and t1.col3 = 'vitaminB'
  or t1.col2 = 'vitaminB' and t1.col3 = 'vitaminA'
)
  and t2.col2 = 'price'
  and cast(t2.col3 as int) < '30';

See SQL Fiddle with Demo

Or you can use a WHERE clause using EXISTS:

select t1.id
from yourtable t1
where 
(
  t1.col2 = 'vitaminA' and t1.col3 = 'vitaminB'
  or t1.col2 = 'vitaminB' and t1.col3 = 'vitaminA'
)
  and exists (select t2.col1
              from yourtable t2
              where t2.col2 = 'price'
                and cast(t2.col3 as int) < 30
                and t1.id = t2.col1)

See SQL Fiddle with Demo

As a side note, your current data structure is very difficult to work with. If possible you might want to consider restructuring your tables.

Answer 2

You need to do a join for this:

select t.col1
from t join
     t tprice
     on t.id = tprice.col1 and
        tprice.col2 = 'price'
where ((t.col2 = 'VitaminA' and t.col3 = 'VitaminB') or
       (t.col2 = 'VitaminB' and t.col3 = 'VitaminA')
      ) and
      (cast(tprice.col3 as int) <= 30)

This is a very arcane data structure. Can you explain where it comes from?