From: codeWarrior <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Subquery alternatives?
Date: Tue, 5 Sep 2006 09:07:36 -0700
Message-ID: <edk77r$1tdd$1@news.hub.org> (raw)
References: <1157116342.151133.121010@b28g2000cwb.googlegroups.com>
I dont think you need the double-left join....
SELECT * FROM STORIES ST
LEFT JOIN TAGS TG ON TG.tagkey = ST.storykey
WHERE TG.tag = "science"
"MRKisThatKid" <mngilbert@gmail.com> wrote in message
news:1157116342.151133.121010@b28g2000cwb.googlegroups.com...
> 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?
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpatnude@hotmail.com
Subject: Re: Subquery alternatives?
In-Reply-To: <edk77r$1tdd$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox