pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

join table problem
2+ messages / 2 participants
[nested] [flat]

* join table problem
@ 2010-07-29 16:35  Jedrin <jrubiando@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Jedrin @ 2010-07-29 16:35 UTC (permalink / raw)
  To: 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






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

* Re: join table problem
@ 2010-08-04 11:11  Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
  parent: Jedrin <jrubiando@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Oliveiros d'Azevedo Cristina @ 2010-08-04 11:11 UTC (permalink / raw)
  To: Jedrin <jrubiando@gmail.com>; pgsql-sql

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 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.
>

You do not specify what is the seed_supplier company name that should appear 
in the case there is more than one

Give this (untested) query a try

SELECT name,MIN(company_name)
FROM plant a
JOIN plant_seed_supplier associative
ON plant_id = a.id
JOIN seed_supplier b
ON b.id = supplier_id
GROUP BY name

Best,
Oliveiros 




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

end of thread, other threads:[~2010-08-04 11:11 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-29 16:35 join table problem Jedrin <jrubiando@gmail.com>
2010-08-04 11:11 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>

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