Request a Demo
Home   >   mailing lists

select with multiple occurences in same table - Mailing list pgsql-sql

From William Herring
Subject select with multiple occurences in same table
Date
Msg-id LNEOLDDDAKFHFMFOADPPCEDICAAA.wherring@mail.ifas.ufl.edu
Whole thread Raw
Responses Re: select with multiple occurences in same table
pl/pgSQL and escaping LIKE clauses
List pgsql-sql
Tree view

      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?

pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Problems using a rule with the WHERE clause
Next
From: "Josh Berkus"
Date:
Subject: Re: select with multiple occurences in same table