Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Multiple Table Joins


YorkshireSteve

Recommended Posts

Posted

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.

Posted

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

Posted

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.

Posted

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.

Posted

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.

Posted

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.

Posted

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.

Posted

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.

Posted

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...