pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselecting rows tagged with "a" but not "b"
6+ messages / 6 participants
[nested] [flat]
* selecting rows tagged with "a" but not "b"
@ 2010-02-01 13:31 8q5tmkyqry@sneakemail.com
0 siblings, 4 replies; 6+ messages in thread
From: 8q5tmkyqry@sneakemail.com @ 2010-02-01 13:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting rows tagged with "a" but not "b"
@ 2010-02-01 13:44 Oliveiros C, <oliveiros.cristina@marktest.pt>
parent: 8q5tmkyqry@sneakemail.com
3 siblings, 0 replies; 6+ messages in thread
From: Oliveiros C, @ 2010-02-01 13:44 UTC (permalink / raw)
To: 8q5tmkyqry@sneakemail.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting rows tagged with "a" but not "b"
@ 2010-02-01 13:50 Andreas Gaab <A.Gaab@scanlab.de>
parent: 8q5tmkyqry@sneakemail.com
3 siblings, 0 replies; 6+ messages in thread
From: Andreas Gaab @ 2010-02-01 13:50 UTC (permalink / raw)
To: '8q5tmkyqry@sneakemail.com' <8q5tmkyqry@sneakemail.com>; 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting rows tagged with "a" but not "b"
@ 2010-02-01 13:56 Milen A. Radev <milen@radev.net>
parent: 8q5tmkyqry@sneakemail.com
3 siblings, 0 replies; 6+ messages in thread
From: Milen A. Radev @ 2010-02-01 13:56 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting rows tagged with "a" but not "b"
@ 2010-02-01 14:14 Andreas Kretschmer <akretschmer@spamfence.net>
parent: 8q5tmkyqry@sneakemail.com
3 siblings, 1 reply; 6+ messages in thread
From: Andreas Kretschmer @ 2010-02-01 14:14 UTC (permalink / raw)
To: 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°
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: selecting rows tagged with "a" but not "b"
@ 2010-02-01 14:52 Yeb Havinga <yebhavinga@gmail.com>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 6+ messages in thread
From: Yeb Havinga @ 2010-02-01 14:52 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-02-01 14:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-01 13:31 selecting rows tagged with "a" but not "b" 8q5tmkyqry@sneakemail.com
2010-02-01 13:44 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2010-02-01 13:50 ` Andreas Gaab <A.Gaab@scanlab.de>
2010-02-01 13:56 ` Milen A. Radev <milen@radev.net>
2010-02-01 14:14 ` Andreas Kretschmer <akretschmer@spamfence.net>
2010-02-01 14:52 ` Yeb Havinga <yebhavinga@gmail.com>
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