opt
id, node, field, optid
1, a, 100, Yes
2, a, 200, Yes
3, b, 100, Yes
4, b, 200, No
skema
id,title
a,x
b,y
Can anybody help me with what to do, if want the title from "skema" only from the post that have the answer Yes in the opt-table. I have tried something like this:
SELECT skema.title FROM skema INNER JOIN opt ON opt.node=skema.id WHERE (opt.field=100 AND opt.optid='Yes') AND (opt.field=200 AND opt.optid='Yes')
What's the magic word that makes such a query work "I want only posts where Yes is selected in both field 100 and 200"... Does it make sense? Can anybody help?
Thank you very much for your reply. None of your queries works, I've already tried them both. The problem with the first is that it doesn't take the "field" into acount and therefore returns all post regardless of both field 100 and field 200 is set to Yes - which is what I need. The second does a kind of the same thing: Finds all posts from opt where field is 100 and optid is yes or field is 200 and optid is yes and join it... that's way to many post, it needs to be some kind of AND construct i think. Of cause they can't both be true, and therefore it was a bad example, but I have tried with groups and such, but I can't figure out how to...
/Asger
EDIT: I havn't made the database layout unfortunately, so I wasn't to decide the column names
SELECT skema.title FROM skema WHERE skema.id IN (SELECT opt1.node FROM opt opt1, opt.op2 WHERE opt1.node=opt2.node AND opt1.field=100 AND opt1.optid='Yes' AND opt2.field=200 AND opt2.optid='Yes')
Hope that'll be usefulll for somebody later...
/Asger