×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Can't think of the right query

Can't think of the right query

Can't think of the right query

(OP)
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?

RE: Can't think of the right query

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:

CODE

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
www.alvechurchdata.co.uk

RE: Can't think of the right query

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

CODE

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

RE: Can't think of the right query

(OP)
Thanks! That seems to be exactly what i need.

RE: Can't think of the right query

Quote:

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
www.alvechurchdata.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close