Complex SQL statement please help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Complex SQL statement please help

Post by mjseaden »

Hi

Code: Select all

select products.*, manufacturers.ManufacturerTitle, classifications.ClassificationTitle from products left join manufacturers,classifications on products.ClassificationID=classifications.ClassificationID and products.ManufacturerID=manufacturers.ManufacturerID limit 0,20
I have three tables - products,manufacturers,classifications. The products table contains the primary keys - ManufacturerID and ClassificationID - of the Manufacturers and Classifications tables, which refer to the titles associated with these keys within these tables.

I want to carry out an SQL query that returns the respective ManufacturerTitles and ClassificationTitles from the Manufacturers and Classifications tables, corresponding to the keys stated against products (ManufacturerID and ClassificationID) in the Products table.

The above query does not work, because I'm not used to using left joins, in fact any joins at all!

I would be grateful for any help.

Many thanks
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Got it. The answer is compound joining:

Code: Select all

select * from Products left join Classifications on Products.ClassificationID = Classifications.ClassificationID left join Manufacturers on Products.ManufacturerID = Manufacturers.ManufacturerID
Post Reply