join table problem

From: Jedrin <jrubiando(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: join table problem
Date: 2010-07-29 16:35:35
Message-ID: 433c09b3-b89e-4c66-9849-944d2d97e0e8@f42g2000yqn.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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: 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 and the seed supplier table since
any plant may have multiple seed suppliers and any seed supplier can
sell seeds for many different plants.

I would like to be able to write a single query that basically does
this:

It will show only one distinct row for each plant in the database. It
will show either just one seed_supplier company name associated with
that plant, or if there is no supplier associated with that plant it
will show the plant in a row with that field empty.

What mechanism does this problem call for, a join or nested query, or
what ?

Thanks

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Lee Hachadoorian 2010-07-29 20:13:44 Re: How to get geometry enabled Tables form Postgresql/postgis
Previous Message Oliveiros d'Azevedo Cristina 2010-07-29 13:31:09 Re: grouping subsets