Joining three tables

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
trent2800
Forum Commoner
Posts: 48
Joined: Mon Oct 02, 2006 7:02 am

Joining three tables

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Can you post their structures?

Code: Select all

SHOW CREATE TABLE foo
trent2800
Forum Commoner
Posts: 48
Joined: Mon Oct 02, 2006 7:02 am

Post 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
trent2800
Forum Commoner
Posts: 48
Joined: Mon Oct 02, 2006 7:02 am

Post by trent2800 »

Never mind, my mind was not working correctly... I had a senior moment... almost 24... already on a steady decline.
Post Reply