pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSequential select queries...??
7+ messages / 7 participants
[nested] [flat]
* Sequential select queries...??
@ 2001-08-15 00:40 Mark Mikulec <mm98au@badger.ac.brocku.ca>
0 siblings, 5 replies; 7+ messages in thread
From: Mark Mikulec @ 2001-08-15 00:40 UTC (permalink / raw)
To: pgsql-sql
Hello,
At first I thought what I was trying to do was simple and could be done
easily - but alas, I've spent way too much time and could not figure out
how to get the results in question.
Let's say I have a table T comprised of id of type integer, name and
description both of type text.
What i'd like to do is the following:
Select id from T where name = 'bleh';
and
Select id from T where description = 'bleh';
and result both results in the same result set. That is, duplicate id's
if they appear. So then I could do a GROUP BY and a COUNT to see how
many appeared in only one, and how many appeared in both.
Could someone help me? I've tried countless different sql queries, can't
seem to get one to work. If I can just get those duplicate id's in the
query.. then I'd be laughing and then I can complete my task.
Thanks in advance,
Mark
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Sequential select queries...??
@ 2001-08-20 16:35 Robby Slaughter <webmaster@robbyslaughter.com>
parent: Mark Mikulec <mm98au@badger.ac.brocku.ca>
4 siblings, 0 replies; 7+ messages in thread
From: Robby Slaughter @ 2001-08-20 16:35 UTC (permalink / raw)
To: Mark Mikulec <mm98au@badger.ac.brocku.ca>; pgsql-sql
>Hello,
>At first I thought what I was trying to do was simple and could be done
>easily - but alas, I've spent way too much time and could not figure out
>how to get the results in question.
I think there's cause for excitement, because unless I'm interpreting you
incorrectly, it IS easy to do what you want to do:
Use the UNION functionality. If your table T is this
id name description
-------------------------
1 abc def
2 bcd abc
3 def ghi
4 jkl bcd
5 hij hij
And you do this:
SELECT id FROM T WHERE name = 'abc'
UNION
SELECT id FROM T WHERE desc = 'abc';
You'll get:
id
----
1
2
That ought to do it for you!
Hope this helps,
Robby Slaughter
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Sequential select queries...??
@ 2001-08-20 16:39 Josh Berkus <josh@agliodbs.com>
parent: Mark Mikulec <mm98au@badger.ac.brocku.ca>
4 siblings, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2001-08-20 16:39 UTC (permalink / raw)
To: Mark Mikulec <mm98au@badger.ac.brocku.ca>; pgsql-sql
Mark,
> What i'd like to do is the following:
>
> Select id from T where name = 'bleh';
>
> and
>
> Select id from T where description = 'bleh';
>
> and result both results in the same result set. That is, duplicate
> id's
> if they appear. So then I could do a GROUP BY and a COUNT to see how
> many appeared in only one, and how many appeared in both.
What you want is UNION ALL. You also want to go out and purchase "SQL
for Smarties" after you finish reading my e-mail.
SELECT id FROM T WHERE name = 'bleh'
UNION ALL
SELECT id FROM T WHERE description = 'bleh';
This gives you both result sets, once right after the other. If you
didn't want to see duplicate values (i.e. only one instance of each
"id"), you would use simply UNION without the "ALL".
This means that it is possible to get both your desired rowcounts out of
a *single* query, using subselects. "SQL for Smarties" can help you
learn to build this kind of query.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Sequential select queries...??
@ 2001-08-20 17:09 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Mark Mikulec <mm98au@badger.ac.brocku.ca>
4 siblings, 0 replies; 7+ messages in thread
From: Ross J. Reedstrom @ 2001-08-20 17:09 UTC (permalink / raw)
To: Mark Mikulec <mm98au@badger.ac.brocku.ca>; +Cc: pgsql-sql
Well, a short answerto your direct question would be:
Select id from T where name = 'bleh'
UNION ALL
Select id from T where description = 'bleh';
But since you described what your trying to do, not just how
your trying to do it, doesn't this do it for you?
SELECT id from T where name = 'bleh' and description = 'bleh';
That should give you just the ones where it appears in both.
Ross
On Tue, Aug 14, 2001 at 08:40:50PM -0400, Mark Mikulec wrote:
> Hello,
>
> At first I thought what I was trying to do was simple and could be done
> easily - but alas, I've spent way too much time and could not figure out
> how to get the results in question.
>
> Let's say I have a table T comprised of id of type integer, name and
> description both of type text.
>
> What i'd like to do is the following:
>
> Select id from T where name = 'bleh';
>
> and
>
> Select id from T where description = 'bleh';
>
> and result both results in the same result set. That is, duplicate id's
> if they appear. So then I could do a GROUP BY and a COUNT to see how
> many appeared in only one, and how many appeared in both.
>
> Could someone help me? I've tried countless different sql queries, can't
> seem to get one to work. If I can just get those duplicate id's in the
> query.. then I'd be laughing and then I can complete my task.
>
> Thanks in advance,
>
> Mark
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Sequential select queries...??
@ 2001-08-20 17:28 Jeff Eckermann <jeckermann@verio.net>
parent: Mark Mikulec <mm98au@badger.ac.brocku.ca>
4 siblings, 0 replies; 7+ messages in thread
From: Jeff Eckermann @ 2001-08-20 17:28 UTC (permalink / raw)
To: Mark Mikulec <mm98au@badger.ac.brocku.ca>; pgsql-sql
Select id from T where name = 'bleh'
UNION ALL
Select id from T where description = 'bleh';
Will get you the resultset you want, but: I don't believe that you can do a
GROUP BY on it.
If you just want counts, as you describe below, you could do something like:
SELECT 'Only One', (SELECT count (id) from T where name = 'bleh' OR
description = 'bleh') - (SELECT count (id) from T where name = 'bleh' AND
description = 'bleh')
UNION
SELECT 'Both', (SELECT count (id) from T where name = 'bleh' AND description
= 'bleh');
----- Original Message -----
From: "Mark Mikulec" <mm98au@badger.ac.brocku.ca>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, August 14, 2001 7:40 PM
Subject: Sequential select queries...??
> Hello,
>
> At first I thought what I was trying to do was simple and could be done
> easily - but alas, I've spent way too much time and could not figure out
> how to get the results in question.
>
> Let's say I have a table T comprised of id of type integer, name and
> description both of type text.
>
> What i'd like to do is the following:
>
> Select id from T where name = 'bleh';
>
> and
>
> Select id from T where description = 'bleh';
>
> and result both results in the same result set. That is, duplicate id's
> if they appear. So then I could do a GROUP BY and a COUNT to see how
> many appeared in only one, and how many appeared in both.
>
> Could someone help me? I've tried countless different sql queries, can't
> seem to get one to work. If I can just get those duplicate id's in the
> query.. then I'd be laughing and then I can complete my task.
>
> Thanks in advance,
>
> Mark
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Sequential select queries...??
@ 2001-08-20 17:43 Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
parent: Mark Mikulec <mm98au@badger.ac.brocku.ca>
4 siblings, 0 replies; 7+ messages in thread
From: Grigoriy G. Vovk @ 2001-08-20 17:43 UTC (permalink / raw)
To: Mark Mikulec <mm98au@badger.ac.brocku.ca>; +Cc: pgsql-sql
I don't knoe, may be I don't understand the question, but for me its
looking like UNION statement. For example:
select id from T where name='bleh'
UNION
select id from T where description='bleh';
Aug 14, 20:40 -0400, Mark Mikulec wrote:
> Hello,
>
> At first I thought what I was trying to do was simple and could be done
> easily - but alas, I've spent way too much time and could not figure out
> how to get the results in question.
>
> Let's say I have a table T comprised of id of type integer, name and
> description both of type text.
>
> What i'd like to do is the following:
>
> Select id from T where name = 'bleh';
>
> and
>
> Select id from T where description = 'bleh';
>
> and result both results in the same result set. That is, duplicate id's
> if they appear. So then I could do a GROUP BY and a COUNT to see how
> many appeared in only one, and how many appeared in both.
>
> Could someone help me? I've tried countless different sql queries, can't
> seem to get one to work. If I can just get those duplicate id's in the
> query.. then I'd be laughing and then I can complete my task.
>
> Thanks in advance,
>
> Mark
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
my best regards,
----------------
Grigoriy G. Vovk
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Sequential select queries...??
@ 2001-08-21 10:43 Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Henshall, Stuart - WCP @ 2001-08-21 10:43 UTC (permalink / raw)
To: 'Mark Mikulec' <mm98au@badger.ac.brocku.ca>; pgsql-sql
If you want to know for each individual one wether both equal or not you
could do:
SELECT *,NOT ((name=description) IS NULL) AND (name=description) AS both
FROM t WHERE name='bleh' OR description='bleh';
If you wanted totals of the same you could do:
SELECT count(*) AS tot,NOT ((name=description) IS NULL) AND
(name=description) AS both FROM t WHERE name='bleh' OR description='bleh'
GROUP BY NOT ((name=description) IS NULL AND (name=description);
I think this should be more efficient than UNIONs, but am not an expert on
the query planner or executor.
- Stuart
> -----Original Message-----
> From: Mark Mikulec [SMTP:mm98au@badger.ac.brocku.ca]
> Sent: Wednesday, August 15, 2001 1:41 AM
> To: pgsql-sql@postgresql.org
> Subject: Sequential select queries...??
>
> Hello,
>
> At first I thought what I was trying to do was simple and could be done
> easily - but alas, I've spent way too much time and could not figure out
> how to get the results in question.
>
> Let's say I have a table T comprised of id of type integer, name and
> description both of type text.
>
> What i'd like to do is the following:
>
> Select id from T where name = 'bleh';
>
> and
>
> Select id from T where description = 'bleh';
>
> and result both results in the same result set. That is, duplicate id's
> if they appear. So then I could do a GROUP BY and a COUNT to see how
> many appeared in only one, and how many appeared in both.
>
> Could someone help me? I've tried countless different sql queries, can't
> seem to get one to work. If I can just get those duplicate id's in the
> query.. then I'd be laughing and then I can complete my task.
>
> Thanks in advance,
>
> Mark
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-08-21 10:43 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-15 00:40 Sequential select queries...?? Mark Mikulec <mm98au@badger.ac.brocku.ca>
2001-08-20 16:35 ` Robby Slaughter <webmaster@robbyslaughter.com>
2001-08-20 16:39 ` Josh Berkus <josh@agliodbs.com>
2001-08-20 17:09 ` Ross J. Reedstrom <reedstrm@rice.edu>
2001-08-20 17:28 ` Jeff Eckermann <jeckermann@verio.net>
2001-08-20 17:43 ` Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
2001-08-21 10:43 RE: Sequential select queries...?? Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>
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