YorkshireSteve Posted February 19, 2004 Posted February 19, 2004 Hi, I'm currently working with the contribution Article Manager. I want to include related articles in my product info pages, based on the x-sell feature of Article Manager. I've only recently discovered joining tables properly in MySQL, and I'm now putting it into practice, however, I'm getting the same results twice when executing this query: select d.articles_name as name, d.articles_id as article_id, au.authors_name as author, au.authors_id as authors_id from articles_xsell as x, articles_description as d, articles as ar, authors as au where x.xsell_id = 8 and d.language_id = 1 and ar.articles_status = 1 and x.articles_id = d.articles_id and au.authors_id = ar.authors_id I'm sure it's likely to be something simple, but I can't quite figure it out. Thanks in advance, Steve.
♥yesudo Posted February 19, 2004 Posted February 19, 2004 Can you put the table structures up here. Your online success is Paramount.
YorkshireSteve Posted February 20, 2004 Author Posted February 20, 2004 Here's the table structure for the four tables I'm accessing: # Table structure for table `articles` # CREATE TABLE `articles` ( `articles_id` int(11) NOT NULL auto_increment, `articles_date_added` datetime NOT NULL default '0000-00-00 00:00:00', `articles_last_modified` datetime default NULL, `articles_date_available` datetime default NULL, `articles_status` tinyint(1) NOT NULL default '0', `authors_id` int(11) default NULL, PRIMARY KEY (`articles_id`), KEY `idx_articles_date_added` (`articles_date_added`) ) TYPE=MyISAM; # -------------------------------------------------------- # Table structure for table `articles_description` # CREATE TABLE `articles_description` ( `articles_id` int(11) NOT NULL auto_increment, `language_id` int(11) NOT NULL default '1', `articles_name` varchar(64) NOT NULL default '', `articles_description` text, `articles_url` varchar(255) default NULL, `articles_viewed` int(5) default '0', `articles_head_title_tag` varchar(80) default NULL, `articles_head_desc_tag` text, `articles_head_keywords_tag` text, PRIMARY KEY (`articles_id`,`language_id`), KEY `articles_name` (`articles_name`) ) TYPE=MyISAM; # -------------------------------------------------------- # Table structure for table `articles_xsell` # CREATE TABLE `articles_xsell` ( `ID` int(10) NOT NULL auto_increment, `articles_id` int(10) unsigned NOT NULL default '1', `xsell_id` int(10) unsigned NOT NULL default '1', `sort_order` int(10) unsigned NOT NULL default '1', PRIMARY KEY (`ID`) ) TYPE=MyISAM; # -------------------------------------------------------- # Table structure for table `authors` # CREATE TABLE `authors` ( `authors_id` int(11) NOT NULL auto_increment, `authors_name` varchar(32) NOT NULL default '', `authors_image` varchar(64) default NULL, `date_added` datetime default NULL, `last_modified` datetime default NULL, PRIMARY KEY (`authors_id`), KEY `IDX_AUTHORS_NAME` (`authors_name`) ) TYPE=MyISAM; Steve
YorkshireSteve Posted February 20, 2004 Author Posted February 20, 2004 And here's some data: # # Dumping data for table `articles` # INSERT INTO `articles` (`articles_id`, `articles_date_added`, `articles_last_modified`, `articles_date_available`, `articles_status`, `authors_id`) VALUES (3, '2004-02-18 11:54:23', '2004-02-20 11:11:38', NULL, 1, 1); INSERT INTO `articles` (`articles_id`, `articles_date_added`, `articles_last_modified`, `articles_date_available`, `articles_status`, `authors_id`) VALUES (4, '2004-02-26 00:00:00', '2004-02-20 11:11:52', NULL, 1, 1); # # Dumping data for table `articles_description` # INSERT INTO `articles_description` (`articles_id`, `language_id`, `articles_name`, `articles_description`, `articles_url`, `articles_viewed`, `articles_head_title_tag`, `articles_head_desc_tag`, `articles_head_keywords_tag`) VALUES (3, 1, 'A Bug\'s Life Review', 'Pur minuscule hero is Flik, an intelligent young ant who is forever inventing \r\nthings to try to make life less routine, much to the amusement of the rest of \r\nthe colony. When the grasshoppers\' share of the food is accidentally destroyed, \r\nit\'s up to Flik to figure out a way to save the colony.', 'www.pixar.com/featurefilms/abl/', 85, 'A Bug\'s Life Review', 'This story of an oppressed ant colony is portrayed by breathtaking computer animation. The same people who brought us Toy Story, Pixar Studios via Disney are responsible for this insect epic.', ''); INSERT INTO `articles_description` (`articles_id`, `language_id`, `articles_name`, `articles_description`, `articles_url`, `articles_viewed`, `articles_head_title_tag`, `articles_head_desc_tag`, `articles_head_keywords_tag`) VALUES (4, 1, 'Microsoft IntelliMouse Explorer Review', '<P><STRONG>Microsoft Optical Technology<BR></STRONG>The fastest, most accurate mouse on the market delivering optical technology. Superior tracking speed translates into precise cursor movement and accuracy.</P>', 'www.microsoft.com/hardware/mouseandkeyboard/productdetails.aspx?pid=003', 15, 'Microsoft IntelliMouse Explorer Review', 'Microsoft Intellimouse Explorer is Microsoft Corporation?s award-winning optical mouse. It features unique comfort enhancements and is supercharged with Microsoft Optical Technology.', ''); # # Dumping data for table `articles_xsell` # INSERT INTO `articles_xsell` (`ID`, `articles_id`, `xsell_id`, `sort_order`) VALUES (8, 4, 26, 1); INSERT INTO `articles_xsell` (`ID`, `articles_id`, `xsell_id`, `sort_order`) VALUES (7, 3, 8, 1); # # Dumping data for table `authors` # INSERT INTO `authors` (`authors_id`, `authors_name`, `authors_image`, `date_added`, `last_modified`) VALUES (1, 'Joe Bloggs', NULL, '2004-02-19 15:43:01', '2004-02-19 16:16:52'); Thanks, Steve.
♥yesudo Posted February 20, 2004 Posted February 20, 2004 for 4 tables you need three joins. I think you only have two - you also need a third join between the two sets of tables. Your online success is Paramount.
♥yesudo Posted February 20, 2004 Posted February 20, 2004 you have: x,d joined au,ar joined so you effectively have two tables still (x,d) and (au,ar) - so you need another join to make one table - (x,d,au,ar) sorry it is early I am still trying to work out the final join - but maybe you have the info you need? Your online success is Paramount.
♥yesudo Posted February 20, 2004 Posted February 20, 2004 try select d.articles_name as name, d.articles_id as article_id, au.authors_name as author, au.authors_id as authors_id from articles_xsell as x, articles_description as d, articles as ar, authors as au where x.xsell_id = 8 and d.language_id = 1 and ar.articles_status = 1 and x.articles_id = d.articles_id and au.authors_id = ar.authors_id and ar.articles_id = d.articles_id additonal join being between article_id in both articles and articles description. Your online success is Paramount.
YorkshireSteve Posted February 20, 2004 Author Posted February 20, 2004 Wow! :D Thanks a million - You've not just got this working, but another piece of the MySQL puzzle's just clicked into place! I'd never looked at joins as actually connecting the tables - I always just thought they were getting data from eachother. Thanks for the help, Emmett! B) Steve.
♥yesudo Posted February 20, 2004 Posted February 20, 2004 no probs. Something I recently read is that the amount of joins required, for a multiple table join, is the amount of tables minus one. Came in helpful for me here. Your online success is Paramount.
YorkshireSteve Posted February 21, 2004 Author Posted February 21, 2004 Suppose that makes sense if you try to visualize the joining: TABLE1 ?join? TABLE2 ?join? TABLE3 ?join? TABLE4 That way, their joins are all 'connectors', whereas previously I had: TABLE1 ?join? TABLE2 TABLE3 ?join? TABLE4
Recommended Posts
Archived
This topic is now archived and is closed to further replies.