Subquery alternatives?
Posted: Fri Sep 01, 2006 8:14 am
Hi, i've posted this in various places but I'm really struggling to find an answer that works for me...
Say I've got a table of stories and a table of tags which apply to the stories.
Say you've been using the tags science and unfinished, and you want all the stories which are science based and are completed.. that is, all the stories tagged with "science" but not "unfinished".
I could do this using sub-queries quite easily, especially the exists / not exists function in mysql.
Unfortunately I'm implementing this kind of query into a web application i'm developing and I want it to be usable on as many different server set-ups as possible.
select * from stories
left join tags as tags1 on (tags1.tagkey = storykey)
left join tags as tags2 on (tags2.tagkey = storykey)
where tags1.tag = "science"
and not tags2.tag = "unfinished"
It would be wonderful if the above worked, but of course it doesn't because when the db engine makes all the possible combinations it will always find a match where tags2 doesn't contain "unfinished".
Can anyone think of a way of achieving this without sub-queries?
... The best solution I've been offered so far is to use group_concat and find_in_set, but these are mysql specific functions and are not portable. Do any of you guys know who I should go about this?
Say I've got a table of stories and a table of tags which apply to the stories.
Say you've been using the tags science and unfinished, and you want all the stories which are science based and are completed.. that is, all the stories tagged with "science" but not "unfinished".
I could do this using sub-queries quite easily, especially the exists / not exists function in mysql.
Unfortunately I'm implementing this kind of query into a web application i'm developing and I want it to be usable on as many different server set-ups as possible.
select * from stories
left join tags as tags1 on (tags1.tagkey = storykey)
left join tags as tags2 on (tags2.tagkey = storykey)
where tags1.tag = "science"
and not tags2.tag = "unfinished"
It would be wonderful if the above worked, but of course it doesn't because when the db engine makes all the possible combinations it will always find a match where tags2 doesn't contain "unfinished".
Can anyone think of a way of achieving this without sub-queries?
... The best solution I've been offered so far is to use group_concat and find_in_set, but these are mysql specific functions and are not portable. Do any of you guys know who I should go about this?