Thread: 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: 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
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