Confused by joins

[zerodefect]

MySQL Server Version - 5.0.81

 

CREATE TABLE `Players` (
`Players_pk` int(10) NOT NULL auto_increment,
`ManufacturersModels_pk` int(10) NOT NULL,
PRIMARY KEY  (`Players_pk`),
KEY `manufacturersmodels_players_fk` (`ManufacturersModels_pk`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `ManufacturersModels` (
`ManufacturersModels_pk` int(10) NOT NULL auto_increment,
`Models_pk` int(10) NOT NULL,
`Manufacturers_pk` int(10) NOT NULL,
PRIMARY KEY  (`ManufacturersModels_pk`),
KEY `manufacturers_manufacturersmodels_fk` (`Manufacturers_pk`),
KEY `models_manufacturersmodels_fk` (`Models_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `Models` (
`Models_pk` int(10) NOT NULL auto_increment,
`Name` varchar(20) NOT NULL,
PRIMARY KEY  (`Models_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `Manufacturers` (
`Manufacturers_pk` int(10) NOT NULL auto_increment,
`Name` varchar(20) NOT NULL,
PRIMARY KEY  (`Manufacturers_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

 

 

Hi,

 

Firstly, my experience with the likes of both of SQL and PHP are rather limited which is slightly hampering me.  I am learning fast though.

 

Having nicely designed the layout of the database via Power*Architect, I'm now at a point where I would like to start implementing some of the functionality.  This is where I'm having the problem.

 

Instead of having a many-to-many relationship with 'Manufacturers' <=> 'Models', I have normalized the relationship further with 'Manufacturers' <=> 'ManufacturersModel' <=> 'Models'.

 

What I am attempting to do is perform a LEFT join from the 'Player' table onto the 'ManufacturersModels' table.  Once I have all these combos, I want to do a join on both the 'Manufacturers' table and the 'Models' table to get the Manufacturer/Model of each player.

 

I think I know how to perform the left join:

SELECT Players.ManufacturersModels_pk FROM Players LEFT JOIN ManufacturersModels ON Players.ManufacturersModels_pk=ManufacturersModels.ManufacturersModels_pk

 

I'm guessing to continue further that I might need to perform a subquery within a query? Having to use both SQL and PHP simultaneously is making it difficult. I know what I want to do, but not how to represent it in code.

 

 

Any tips/suggestions would be much appreciated...

 

zerodefect

 

[cags]

It's possible you can just do another join. It all depends on exactly what results you want returning. But....

 

SELECT Players.ManufacturersModels_pk 
FROM Players 
LEFT JOIN ManufacturersModels 
ON Players.ManufacturersModels_pk=ManufacturersModels.ManufacturersModels_pk
JOIN Manufacturers
ON ManufacturersModels.Manufacturers_pk=Manufacturers.Manufacturers_pk
JOIN Models
ON ManufacturersModels.Models_pk=Models.Models_pk