SQL question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

SQL question

Post by asgerhallas »

Hi,

I have these two tables (simplicified):

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:

Code: Select all

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?
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Try:

Code: Select all

SELECT skema.title
FROM skema, opt
WHERE opt.optid = 'Yes'
AND opt.node = skema.id
I think that should do it.

EDIT: I think your query would have worked if you had changed the AND to an OR:

Code: Select all

WHERE (opt.field=100 AND opt.optid='Yes') OR (opt.field=200 AND opt.optid='Yes')
EDIT 2: Don't use reserved SQL words as names for your tables or columns.
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

Post by asgerhallas »

Hi,

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 :)
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

Post by asgerhallas »

Ah solved it... the solution is of cause a self join and then combined with a inner join or a sub query...

Untested code (but tested another form of it):

Code: Select all

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
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Ah - sorry. I didn't understand that you needed both 100 and 200 to be Yes - I misread that part of your post.

Glad you sorted it anyway.
Post Reply