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=5000 and printed='yes' it does not retrieve anything.
What am i doing wrong?

 

[DotNetGnat]

try this:

Select formulaID from mytable
WHERE criterion='length' OR value=6000

-DNG

 

[yelworcm]

select *
from mytable a join 
   mytable b on a.FormulaID = b.formulaID
where a.value = '6000' 
  and a.criterion = 'length'
  and b.value = 'yes'
  and b.criterion = 'printed'