select with multiple occurences in same table

From: "William Herring" <wherring(at)mail(dot)ifas(dot)ufl(dot)edu>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: select with multiple occurences in same table
Date: 2001-06-26 00:08:39
Message-ID: LNEOLDDDAKFHFMFOADPPCEDICAAA.wherring@mail.ifas.ufl.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice 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?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Robby Slaughter 2001-06-26 00:50:48 pl/pgSQL and escaping LIKE clauses
Previous Message Josh Berkus 2001-06-25 22:37:39 Re: select with multiple occurences in same table

Browse pgsql-sql by date

  From Date Subject
Next Message Robby Slaughter 2001-06-26 00:50:48 pl/pgSQL and escaping LIKE clauses
Previous Message Josh Berkus 2001-06-25 22:37:39 Re: select with multiple occurences in same table