Can't think of the right query

[abenstex]

Hi everyone,

i have a table with one field called "formulaId", another called "criterion" and another called "value". The thing is that for each formula, there are more criteria possible and of course more values as each criterion needs a value. For example:

formulaId criterion value
fm01 length 6000
fm01 printed yes

So now i want the formulaId where the criterion is length = 6000 AND printed='yes' but if i create a where clause like:
select formulaId from mytable where length=6000 and printed='yes' it does not retrieve anything.
What am i doing wrong?

 

[alvechurchdata]

Your SQL is failing because printed and length are not names of fields. The design of the table makes this a difficult query but I'd try a subquery:

Select formulaId from myTable 
  Where criterion = "length" and value=6000 and
  formulaID In 
  (Select formulaId from myTable 
     Where criterion = "printed" and value="yes")

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[r937]

going the subquery route will be difficult when there are more than two conditions

select formulaId 
  from myTable 
 where criterion='length' and value='6000'
    or criterion='printed' and value='yes'
group
    by formulaId
having count(*) = 2

r937.com | rudy.ca

 

[abenstex]

Thanks! That seems to be exactly what i need.

 

[alvechurchdata]

going the subquery route will be difficult when there are more than two conditions

You're right. I was writing a series of sub queries when I responded.

"Everything looks like a nail when you're holding a hammer"

Geoff Franklin

http://www.alvechurchdata.co.uk