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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
