Page 1 of 1

Joining three tables

Posted: Tue Nov 21, 2006 10:46 pm
by trent2800
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[syntax="sql"]
SELECT `CATEGORY`.`Name`,`INCENSE2`.*, `INCENSE_SIZE`.`QOH`,`INCENSE_SIZE`.`Price`,`SIZE`.`Name` FROM `INCENSE_SIZE`, `SIZE`, `INCENSE2` JOIN `CATEGORY` USING (CategoryID) WHERE `INCENSE2`.`PRODUCTID` = `INCENSE_SIZE`.`ProductID` AND `SIZE`.`SIZEID` = `INCENSE_SIZE`.`SIZEID` AND `INCENSE2`.`CategoryID` = `CATEGORY`.`CategoryID`
This obviously doesnt work. How would I do this? I have 4 tables. INCENSE2, CATEGORY, INCENSE_SIZE, SIZE. INCENSE2, INCENSE_SIZE and SIZE are a many to many relationship. I also need to join INCENSE and CATEGORY at CategoryID to prevent data replication.


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Nov 21, 2006 11:27 pm
by feyd
Can you post their structures?

Code: Select all

SHOW CREATE TABLE foo

Posted: Wed Nov 22, 2006 12:17 pm
by trent2800

Code: Select all

CREATE TABLE `INCENSE2` (\n  
`ProductID` int(11) NOT NULL auto_increment,\n  
`Name` varchar(50) NOT NULL default '',\n  
`Desc` text NOT NULL,\n  `CategoryID` int(11) NOT NULL default '0',\n  
PRIMARY KEY  (`ProductID`),\n  
KEY `FKCatID` (`CategoryID`)\n
) 
ENGINE=MyISAM 
AUTO_INCREMENT=37 
DEFAULT CHARSET=latin1

CREATE TABLE `INCENSE_SIZE` (\n  
`ProductID` int(11) NOT NULL default '0',\n  
`SizeID` int(11) NOT NULL default '0',\n 
`QOH` int(11) NOT NULL default '0',\n  
`Price` decimal(10,2) NOT NULL default '0.00',\n  
PRIMARY KEY  (`ProductID`),\n  KEY `SizeID` (`SizeID`)\n) 
ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Bridge table between INCENSE and SIZE'

CREATE TABLE `SIZE` (\n  
`SizeID` int(11) NOT NULL auto_increment,\n  
`Name` varchar(50) NOT NULL default '',\n  
PRIMARY KEY  (`SizeID`)\n
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COMMENT='Sizes for incense'

CREATE TABLE `CATEGORY` (\n 
`CategoryID` int(11) NOT NULL auto_increment,\n  
`Name` varchar(20) NOT NULL default '',\n  
`Desc` text NOT NULL,\n  
`Parent` int(11) default NULL,\n  
PRIMARY KEY  (`CategoryID`)\n) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=latin1

Posted: Wed Nov 22, 2006 9:07 pm
by trent2800
Never mind, my mind was not working correctly... I had a senior moment... almost 24... already on a steady decline.