Re: [SQL] join table problem

Howdy! At any rate, say I have 3 tables: table plant id:integer name string table seed_supplier id: integer company_name: string table plant_seed_supplier plant_id seed_supplier_id plant_seed_supplier is a join table that supports a many to many relationship between the plant table an

[SQL] join table problem

I have a specific problem with a join table and I've simplified it to these 3 tables so as not to have to post anything that hints towards the business nature of our database just in case .. At any rate, say I have 3 tables: table plant id:integer name string table seed_supplier id: in

[SQL] join table?

Hello, list. First post here, I think. So I've worked with several *lousy* DB engines and *poorly* designed databases behind crappy applications over these many years. I look forward to moving everything to PG. First issue is addressed. Now with the grand opportunity to be hoist upon my own pet

Re: [SQL] join table with itself?

William Leite Araújo wrote: On 15/03/07, *T E Schmitz* <[EMAIL PROTECTED] > wrote: (...) Try join the tables. SELECT present.day, present.low, (MIN(future.day)-present.day) as days2fall FROM history AS present JOIN history AS future ON ( present.day

Re: [SQL] join table with itself?

On 15/03/07, T E Schmitz <[EMAIL PROTECTED]> wrote:(...) Try join the tables. SELECT present.day, present.low, (MIN(future.day)-present.day) as days2fall FROM history AS present JOIN history AS future ON ( present.day < future.day AND future.low <= present.low ) GROUP BY present.day,

[SQL] join table with itself?

I am not getting to grips with the following query: set-up: Postgresql 8.1 The table HISTORY contains stockmarket data: DAY HIGHLOW 2007/02/28 6286.1 6166.2 2007/02/27 6434.7 6270.5 2007/02/26 6446.8 6401.5 I'd like to produce the following resul

Re: [SQL] Join Table

T E Schmitz wrote: On Mon, Nov 01, 2004 at 04:34:32PM +, T E Schmitz wrote: Question: is it necessary/advisable to create an index for the ITEM_FK column? Or is this redundantbecause this column is already one of the PK columns? However, read the "Multicolumn Indexes" section in the "Indexes"

Re: [SQL] Join Table

On Mon, Nov 01, 2004 at 06:12:02PM +, T E Schmitz wrote: > I see. If using a multi-column PK, the order matters. > So, if I want to access the table both via the 1st and 2nd PK column, I > would have to define an index for the 2nd column to avoid a full table scan. If you want to use an inde

Re: [SQL] Join Table

Hello Mike/Tom/Richard, Thank you for your replies. Michael Fuhr wrote: On Mon, Nov 01, 2004 at 04:34:32PM +, T E Schmitz wrote: Question: is it necessary/advisable to create an index for the ITEM_FK column? Or is this redundantbecause this column is already one of the PK columns? However, r

Re: [SQL] Join Table

T E Schmitz wrote: Hello, I have created the following join table: the two FKs are the PK of the table. Typically, I will need to select rows for a given ITEM_FK. Question: is it necessary/advisable to create an index for the ITEM_FK column? Or is this redundantbecause this column is already one

Re: [SQL] Join Table

T E Schmitz <[EMAIL PROTECTED]> writes: > CREATE TABLE SUPPLY > ( > ITEM_FK integer NOT NULL, > CONTACT_FK integer NOT NULL, > COST numeric (7,2), > PRIMARY KEY (ITEM_FK,CONTACT_FK) > ); > Question: is it necessary/advisable to create an index for the ITEM_FK > column? Or is this redundantbecause

Re: [SQL] Join Table

On Mon, Nov 01, 2004 at 04:34:32PM +, T E Schmitz wrote: > > I have created the following join table: the two FKs are the PK of the > table. Typically, I will need to select rows for a given ITEM_FK. > > Question: is it necessary/advisable to create an index for the ITEM_FK > column? Or is

[SQL] Join Table

Hello, I have created the following join table: the two FKs are the PK of the table. Typically, I will need to select rows for a given ITEM_FK. Question: is it necessary/advisable to create an index for the ITEM_FK column? Or is this redundantbecause this column is already one of the PK columns

Re: [SQL] Join table with itself for heirarchial system?

You might look on our contrib/ltree module (http://www.sai.msu.su/~megera/postgres/gist/ltree) Oleg On Wed, 16 Jul 2003, Benjamin Smith wrote: > Can you query a set of nested entries to simulate a heirarchial system with a > single query? > > I'm building a nested category table with a de

[SQL] Join table with itself for heirarchial system?

Can you query a set of nested entries to simulate a heirarchial system with a single query? I'm building a nested category table with a definition like below" CREATE TABLE category ( id serial, parent integer not null, title varchar); Idea is that we can "nest" categories so that we have