pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Sequential 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