Thread: selecting rows tagged with "a" but not "b"
Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? what I'd like to do is: <wishful thinking> select articleID from tags where tag="a" SUBTRACT select articleID from tags where tab="b" </wishful thinking> how do I do this in real SQL? thanks Darrell
8q5tmkyqry@sneakemail.com написа: > Hi, > > I have a two tables: > > article > articleID, name, content > > tags > articleID, tag > > I want to find all articles that are tagged with "a" but not "b" > > how do I do this? > > what I'd like to do is: > > <wishful thinking> > select articleID from tags where tag="a" > SUBTRACT > select articleID from tags where tab="b" > </wishful thinking> > > how do I do this in real SQL? Replace "SUBSTRACT" with "EXCEPT" (http://www.postgresql.org/docs/current/static/sql-select.html#SQL-EXCEPT). -- Milen A. Radev
Darrell, Can you provide a little more information and background on your problem. please? What values can the "tag" column assume? Just "a" and "b" ? Both? Please give examples of table contents and desired output, your mail doesn't contain enough info to give you more advises Thank you Best, Oliveiros ----- Original Message ----- From: <8q5tmkyqry@sneakemail.com> To: <pgsql-sql@postgresql.org> Sent: Monday, February 01, 2010 1:31 PM Subject: [SQL] selecting rows tagged with "a" but not "b" > Hi, > > I have a two tables: > > article > articleID, name, content > > tags > articleID, tag > > I want to find all articles that are tagged with "a" but not "b" > > how do I do this? > > what I'd like to do is: > > <wishful thinking> > select articleID from tags where tag="a" > SUBTRACT > select articleID from tags where tab="b" > </wishful thinking> > > how do I do this in real SQL? > > thanks > > Darrell > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Hi Darrell, SELECT DISTINCT articleID FROM tags WHERE tag = "a" EXCEPT SELECT DISTINCT articleID FROM tags WHERE tag = "b"; Regards, Andreas -----Ursprüngliche Nachricht----- Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] Im Auftrag von 8q5tmkyqry@sneakemail.com Gesendet: Montag, 1. Februar 2010 14:32 An: pgsql-sql@postgresql.org Betreff: [SQL] selecting rows tagged with "a" but not "b" Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? what I'd like to do is: <wishful thinking> select articleID from tags where tag="a" SUBTRACT select articleID from tags where tab="b" </wishful thinking> how do I do this in real SQL? thanks Darrell -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
8q5tmkyqry@sneakemail.com <8q5tmkyqry@sneakemail.com> wrote: > Hi, > > I have a two tables: > > article > articleID, name, content > > tags > articleID, tag > > I want to find all articles that are tagged with "a" but not "b" > > how do I do this? select a.* from article left join tags t on a.articleID=t.articleID where b.tag = 'a'; Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer wrote: > 8q5tmkyqry@sneakemail.com <8q5tmkyqry@sneakemail.com> wrote: > > >> Hi, >> >> I have a two tables: >> >> article >> articleID, name, content >> >> tags >> articleID, tag >> >> I want to find all articles that are tagged with "a" but not "b" >> >> how do I do this? >> > > select a.* from article left join tags t on a.articleID=t.articleID where b.tag = 'a'; > select a.* from article left join tags t on a.articleID=t.articleID where t.tag = 'a' where not exists (select * from tags t2 where t2.articleID=a.articleID and t2.tag = 'b'); Yeb