I have to perform a select on a mysql database using php.

here is an example of my tables

Division Table:
ID | Name

Players Table:
ID | Name | TeamID(FK)

Team Table:

ID | Name | DivID(FK)

Ok, here is the problem. I have a previous script I would like to modify. The old script pulled the Team Name from the team table into a dropdown menu and passed the ID variable to the next page where I queried the players by TeamID from the players table and emailed everyone on that team.
What I would like to do now is use the same script and modify it the least amount as possible. I was thinking an outer join, but I don't know exactly how to write it. I now have the drop down menu pulling the Division Name from the Division table and it passes the ID from the Division table. How would I pull the players names by Division when they do not have a DivID in the players table?

Thanks, Paul

Either of these should work

SELECT P.name, D.name AS Dname FROM players AS P,team as T LEFT JOIN division AS D ON T.divid = D.id where P.id = [playerID] and T.id = p.teamid ;


SELECT P.name, D.name AS Dname FROM players AS P left join team as T ON T.id = P.teamid JOIN division AS D ON T.divid = D.id where P.id = [playerID] ;

Originally Posted by chrishirst

Either of these should work

SELECT P.name, D.name AS Dname FROM players AS P,team as T LEFT JOIN division AS D ON T.divid = D.id where P.id = [playerID] and T.id = p.teamid ;


SELECT P.name, D.name AS Dname FROM players AS P left join team as T ON T.id = P.teamid JOIN division AS D ON T.divid = D.id where P.id = [playerID] ;

Ok, I'm a little confused because I do not understand AS i guess. Any good reading on AS? Is this a random name? Is that the variable? Below are my tables:

CREATE TABLE `cclpV101RC1divisions` (
  `id` int(4) unsigned zerofill NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `list_order` int(3) NOT NULL default '1',
  `disable` int(1) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE `cclpV101RC1players` (
  `id` int(10) unsigned zerofill NOT NULL auto_increment,
  `lname` varchar(50) NOT NULL default '',
  `fname` varchar(50) NOT NULL default '',
  `birthday` date NOT NULL default '0000-00-00',
  `email` varchar(200) NOT NULL default '',
  `hphone` varchar(20) NOT NULL default '',
  `wphone` varchar(20) NOT NULL default '',
  `address` mediumtext NOT NULL,
  `teamid` int(4) unsigned zerofill NOT NULL default '0000',
  `position` varchar(10) NOT NULL default '',
  `number` int(3) NOT NULL default '0',
  `info` text NOT NULL,
  `photo` varchar(100) NOT NULL default '',
  `registered` int(1) NOT NULL default '0',
  `requested` int(1) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=56 ;

CREATE TABLE `cclpV101RC1teams` (
  `id` int(4) unsigned zerofill NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `contact_name` varchar(70) NOT NULL default '',
  `contact_email` varchar(70) NOT NULL default '',
  `hphone` varchar(20) NOT NULL default '',
  `wphone` varchar(20) NOT NULL default '',
  `address` mediumtext NOT NULL,
  `division` varchar(50) NOT NULL default '',
  `password` varchar(50) NOT NULL default '',
  `info` longtext NOT NULL,
  `photo` varchar(100) NOT NULL default '',
  `photo_cap` mediumtext NOT NULL,
  `disabled` int(1) NOT NULL default '1',
  `dstats` int(1) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

AS simply assigns an alias, so instead of using cclpV101RC1players.name to refer to the name column in the player table.

having cclpV101RC1players AS P allows you to refer to it as P.name

http://www.w3schools.com/sql/sql_alias.asp

Originally Posted by chrishirst

AS simply assigns an alias, so instead of using cclpV101RC1players.name to refer to the name column in the player table.

having cclpV101RC1players AS P allows you to refer to it as P.name

http://www.w3schools.com/sql/sql_alias.asp

Ok, easy enough. Now to take it a step further...

SELECT P.name, D.name AS Dname FROM players AS P,team as T LEFT JOIN division AS D ON T.divid = D.id where P.id = [playerID] and T.id = p.teamid ;

You are selecting cclpV101RC1players.name and cclpV101RC1divisions.name (grouping them together??) AS (the alias) Dname

You are selecting cclpV101RC1players.name and cclpV101RC1divisions.name (grouping them together??) AS (the alias) Dname

Nope

Only the division name is Dname otherwise you would have two columns in the recordset called "name"

you could have P.name AS Pname, D.name AS Dname