pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: select with multiple occurences in same table
2+ messages / 2 participants
[nested] [flat]

* Re: select with multiple occurences in same table
@ 2001-06-25 22:37  Josh Berkus <josh@agliodbs.com>
  parent: William Herring <wherring@mail.ifas.ufl.edu>
  0 siblings, 0 replies; 2+ messages in thread

From: Josh Berkus @ 2001-06-25 22:37 UTC (permalink / raw)
  To: William Herring <wherring@mail.ifas.ufl.edu>; +Cc: pgsql-sql

WIlliam,

>       Table "inventorytrack"
>  Attribute  |   Type    | Modifier
> ------------+-----------+----------
>  tattoo     | text      |
>  past_group | text      |
>  ranch      | text      |
>  ent_date   | timestamp |
>  indate     | date      |
> 

Are you sure that you're using the most efficient data types for this
table?  And where's your primary key?


> In the above table, I have multiple occurrences of ‘tattoo’.
> However, some
> of the tattoo’s have null value in ‘indate’, some do not.  I would
> like to
> select those tattoos with a null indate, unless there is another
> identical
> tattoo in the table that has  a date value in  ‘indate’.

THis one is easy.  However, I'm not sure exactly waht you want; the
sample return tuples you provided do not match what you asked for!

If you wanted to answer the question above:

SELECT * FROM inventorytrack
WHERE indate IS NULL
AND NOT EXISTS (
	SELECT tattoo FROM inventorytrack it2
	WHERE it2.tattoo = inventorytrack.tattoo
		AND indate IS NOT NULL );

-Josh Berkus



______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] 2+ messages in thread

* select with multiple occurences in same table
@ 2001-06-26 00:08  William Herring <wherring@mail.ifas.ufl.edu>
  0 siblings, 1 reply; 2+ messages in thread

From: William Herring @ 2001-06-26 00:08 UTC (permalink / raw)
  To: pgsql-sql

      Table "inventorytrack"
 Attribute  |   Type    | Modifier
------------+-----------+----------
 tattoo     | text      |
 past_group | text      |
 ranch      | text      |
 ent_date   | timestamp |
 indate     | date      |


In the above table, I have multiple occurrences of ‘tattoo’.  However, some
of the tattoo’s have null value in ‘indate’, some do not.  I would like to
select those tattoos with a null indate, unless there is another identical
tattoo in the table that has  a date value in  ‘indate’.  For example, I
might have:

tattoo |   past_group   | ranch  |        ent_date        |   indate
--------+----------------+--------+------------------------+------------
 1039   | Barn2/Behind   | Iberia | 2000-03-16 11:03:32-08 |
 1039   | Across/Bubs    | Iberia | 2000-12-28 14:57:13-08 | 2000-12-28
 1832   | GardnerBlock#3 | Iberia | 2000-03-16 11:03:32-08 |
1832     | GardnerBlock#2 | Iberia | 2000-12-28 12:46:57-08 | 2000-12-28
1833     | Becker1            | Iberia | 2000-03-16 11:03:32-08 |




So the prospective SELECT would give back:

tattoo |   past_group   | ranch  |        ent_date        |   indate
1039   | Across/Bubs    | Iberia | 2000-12-28 14:57:13-08 | 2000-12-28
1832| GardnerBlock#2 | Iberia | 2000-12-28 12:46:57-08 | 2000-12-28
1833| Becker1            | Iberia | 2000-03-16 11:03:32-08 |

Any suggestions on how to do this?

^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-06-26 00:08 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-26 00:08 select with multiple occurences in same table William Herring <wherring@mail.ifas.ufl.edu>
2001-06-25 22:37 ` Josh Berkus <josh@agliodbs.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