[SOLVED] Inner JOIN on 3 tables giving me 3 duplicate rows???

[mastro97]

Hello,

 

Trying to get some fields out of three tables. It gives me the data fine, but the result set seems to have each row duplicated three times.  In plain english, I have these 3 tables and I am trying to match them all on the product_id and pull out one joined row for each product_id if the product category is 'X'.  so I have id, price, image, and name.  for example:

 

This is what I want it to be like..

3    45.00  image1.jpg    blue shirt

7    64.99  image23.jpg  orange shirt

 

This is what I seem to be getting..

3    45.00  image1.jpg    blue shirt

3    45.00  image1.jpg    blue shirt

3    45.00  image1.jpg    blue shirt

7    64.99  image23.jpg  orange shirt

7    64.99  image23.jpg  orange shirt

7    64.99  image23.jpg  orange shirt

 

Thanks so much for anyone who can help me with why this isn't working...

 

MySql command im using.

SELECT products.products_id, products.products_price, products.products_image, products_description.products_name 
FROM products 
INNER JOIN products_to_categories ON products.products_id=products_to_categories.products_id 
INNER JOIN products_description ON products.products_id=products_description.products_id 
where products_to_categories.categories_id=10 ORDER BY products_id ASC

 

Here is my table setup:

products_to_categories:

 CREATE TABLE `products_to_categories` (
`products_id` int(11) NOT NULL default '0',
`categories_id` int(11) NOT NULL default '0',
PRIMARY KEY  (`products_id`,`categories_id`)
) TYPE=MyISAM 

 

products_description:

CREATE TABLE `products_description` (
`products_id` int(11) NOT NULL auto_increment,
`language_id` int(11) NOT NULL default '1',
`products_name` varchar(64) NOT NULL default '',
`products_description` text,
`products_url` varchar(255) default NULL,
`products_viewed` int(5) default '0',
PRIMARY KEY  (`products_id`,`language_id`),
KEY `products_name` (`products_name`)
) TYPE=MyISAM

 

products:

CREATE TABLE `products` (
`products_id` int(11) NOT NULL auto_increment,
`products_quantity` int(4) NOT NULL default '0',
`products_model` varchar(12) default NULL,
`products_image` varchar(64) default NULL,
`products_price` decimal(15,4) NOT NULL default '0.0000',
`products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
`products_last_modified` datetime default NULL,
`products_date_available` datetime default NULL,
`products_weight` decimal(5,2) NOT NULL default '0.00',
`products_status` tinyint(1) NOT NULL default '0',
`products_tax_class_id` int(11) NOT NULL default '0',
`manufacturers_id` int(11) default NULL,
`products_ordered` int(11) NOT NULL default '0',
PRIMARY KEY  (`products_id`),
KEY `idx_products_model` (`products_model`),
KEY `idx_products_date_added` (`products_date_added`),
KEY `manufacturers_id` (`manufacturers_id`)
) TYPE=MyISAM

 

 

Unitl i can get it to work i am using this temp code just to output the data so i can see:

			$products_query = tep_db_query("SELECT products.products_id, products.products_price, products.products_image, products_description.products_name 
FROM products 
INNER JOIN products_to_categories ON products.products_id=products_to_categories.products_id 
INNER JOIN products_description ON products.products_id=products_description.products_id 
where products_to_categories.categories_id=10 
ORDER BY products_id ASC");
		$count = 0;
while (list($products_id, $products_price, $products_image, $products_name) = mysql_fetch_row($products_query)) {
$count++;

echo($products_id . '<BR><BR>');
echo($products_price . '<BR><BR>');
echo($products_image . '<BR><BR>');
}

echo($count);

 

 

[fenway]

I would use COUNT(*) or mysql_num_rows() to make sure you're actually getting back duplicate rows... seems very strange to me.

[mastro97]

Thanks, but it was really strange.  So strange in fact that it got me thinking about my old C++ class and how sometimes the stupidest most basic things can trip you up. 

 

It turns out that there are really in fact 3 entries for each products_id in the database table for products_description.  One for every language_id...

 PRIMARY KEY  (`products_id`,`language_id`),

 

 

so i just addded

AND products_description.language_id=1

and everything is all good.