I was hoping someone could see something wrong with the following SELECT:

The part after the UNION is fine - works as expected.

The first part gives unexpected results though. It gives me 8 of each product_attribute.reference (in separate rows) with different quantities for each.

I'm baffled on this one. Hopefully someone has sharper eyes than I do.

Cheers,

Ron

It is impossible to know this without having your data model and an explanation of what should be stored in each table. I suspect that you either have duplicate entries in one of the tables or you are missing a where clause entry filtering even more.

I was afraid of that.

I'll just do the trial and error thing and I'll get it sooner or later.

Thanks.

...or you could get really wild and crazy and post the pk/fk information that we need to help you!

-PatP

Point taken

product Table:

1 id_product int(10)
2 id_supplier int(10)
3 id_manufacturer int(10)
4 id_category_default int(10)
5 id_shop_default int(10)
6 id_tax_rules_group int(11)
7 on_sale tinyint(1)
8 online_only tinyint(1)
9 ean13 varchar(13)
10 upc varchar(12)
11 ecotax decimal(17,6)
12 quantity int(10)
13 minimal_quantity int(10)
14 price decimal(20,6)
15 wholesale_price decimal(20,6)
16 unity varchar(255)
17 unit_price_ratio decimal(20,6)
18 additional_shipping_cost decimal(20,2)
19 reference varchar(32)
20 supplier_reference varchar(32)
21 location varchar(64)
22 width decimal(20,6)
23 height decimal(20,6)
24 depth decimal(20,6)
25 weight decimal(20,6)
26 out_of_stock int(10)
27 quantity_discount tinyint(1)
28 customizable tinyint(2)
29 uploadable_files tinyint(4)
30 text_fields tinyint(4)
31 active tinyint(1)
32 available_for_order tinyint(1)
33 available_date date
34 condition enum('new', 'used', 'refurbished')
35 show_price tinyint(1)
36 indexed tinyint(1)
37 visibility enum('both', 'catalog', 'search', 'none')
38 cache_is_pack tinyint(1)
39 cache_has_attachments tinyint(1)
40 is_virtual tinyint(1)
41 cache_default_attribute int(10)
42 date_add datetime
43 date_upd datetime
44 advanced_stock_management tinyint(1)

product_attribute Table:

1 id_product_attribute int(10)
2 id_product int(10)
3 reference varchar(32)
4 supplier_reference varchar(32)
5 location varchar(64)
6 ean13 varchar(13)
7 upc varchar(12)
8 wholesale_price decimal(20,6)
9 price decimal(20,6)
10 ecotax decimal(17,6)
11 quantity int(10)
12 weight decimal(20,6)
13 unit_price_impact decimal(17,2)
14 default_on tinyint(1)
15 minimal_quantity int(10)
16 available_date date

specific_price Table:

1 id_specific_price int(10)
2 id_specific_price_rule int(11)
3 id_cart int(11)
4 id_product int(10)
5 id_shop int(11)
6 id_shop_group int(11)
7 id_currency int(10)
8 id_country int(10)
9 id_group int(10)
10 id_customer int(10)
11 id_product_attribute int(10)
12 price decimal(20,6)
13 from_quantity mediumint(8)
14 reduction decimal(20,6)
15 reduction_type enum('amount', 'percentage')
16 from datetime
17 to datetime

stock_available Table:

1 id_stock_available int(11)
2 id_product int(11)
3 id_product_attribute int(11)
4 id_shop int(11)
5 id_shop_group int(11)
6 quantity int(10)
7 depends_on_stock tinyint(1)
8 out_of_stock tinyint(1)

What more information do I need to supply to help you help me.

The fields themselves are being pulled from the correct tables (there are some duplicates across tables that are really only there for backward compatibility).

Thanks to all that can help.

Cheers,

Ron

in mysql, the best way to show DDL, including PK/FK relationships, is to run SHOW CREATE TABLE for each table, e.g.

Then that's what I'll do

CREATE TABLE `product` (
`id_product` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_supplier` int(10) unsigned DEFAULT NULL,
`id_manufacturer` int(10) unsigned DEFAULT NULL,
`id_category_default` int(10) unsigned DEFAULT NULL,
`id_shop_default` int(10) unsigned NOT NULL DEFAULT '1',
`id_tax_rules_group` int(11) unsigned NOT NULL,
`on_sale` tinyint(1) unsigned NOT NULL DEFAULT '0',
`online_only` tinyint(1) unsigned NOT NULL DEFAULT '0',
`ean13` varchar(13) DEFAULT NULL,
`upc` varchar(12) DEFAULT NULL,
`ecotax` decimal(17,6) NOT NULL DEFAULT '0.000000',
`quantity` int(10) NOT NULL DEFAULT '0',
`minimal_quantity` int(10) unsigned NOT NULL DEFAULT '1',
`price` decimal(20,6) NOT NULL DEFAULT '0.000000',
`wholesale_price` decimal(20,6) NOT NULL DEFAULT '0.000000',
`unity` varchar(255) DEFAULT NULL,
`unit_price_ratio` decimal(20,6) NOT NULL DEFAULT '0.000000',
`additional_shipping_cost` decimal(20,2) NOT NULL DEFAULT '0.00',
`reference` varchar(32) DEFAULT NULL,
`supplier_reference` varchar(32) DEFAULT NULL,
`location` varchar(64) DEFAULT NULL,
`width` decimal(20,6) NOT NULL DEFAULT '0.000000',
`height` decimal(20,6) NOT NULL DEFAULT '0.000000',
`depth` decimal(20,6) NOT NULL DEFAULT '0.000000',
`weight` decimal(20,6) NOT NULL DEFAULT '0.000000',
`out_of_stock` int(10) unsigned NOT NULL DEFAULT '2',
`quantity_discount` tinyint(1) DEFAULT '0',
`customizable` tinyint(2) NOT NULL DEFAULT '0',
`uploadable_files` tinyint(4) NOT NULL DEFAULT '0',
`text_fields` tinyint(4) NOT NULL DEFAULT '0',
`active` tinyint(1) unsigned NOT NULL DEFAULT '0',
`available_for_order` tinyint(1) NOT NULL DEFAULT '1',
`available_date` date NOT NULL,
`condition` enum('new','used','refurbished') NOT NULL DEFAULT 'new',
`show_price` tinyint(1) NOT NULL DEFAULT '1',
`indexed` tinyint(1) NOT NULL DEFAULT '0',
`visibility` enum('both','catalog','search','none') NOT NULL DEFAULT 'both',
`cache_is_pack` tinyint(1) NOT NULL DEFAULT '0',
`cache_has_attachments` tinyint(1) NOT NULL DEFAULT '0',
`is_virtual` tinyint(1) NOT NULL DEFAULT '0',
`cache_default_attribute` int(10) unsigned DEFAULT NULL,
`date_add` datetime NOT NULL,
`date_upd` datetime NOT NULL,
`advanced_stock_management` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id_product`),
KEY `product_supplier` (`id_supplier`),
KEY `product_manufacturer` (`id_manufacturer`),
KEY `id_category_default` (`id_category_default`),
KEY `indexed` (`indexed`),
KEY `date_add` (`date_add`)
) ENGINE=InnoDB AUTO_INCREMENT=2366 DEFAULT CHARSET=utf8

CREATE TABLE `product_attribute` (
`id_product_attribute` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_product` int(10) unsigned NOT NULL,
`reference` varchar(32) DEFAULT NULL,
`supplier_reference` varchar(32) DEFAULT NULL,
`location` varchar(64) DEFAULT NULL,
`ean13` varchar(13) DEFAULT NULL,
`upc` varchar(12) DEFAULT NULL,
`wholesale_price` decimal(20,6) NOT NULL DEFAULT '0.000000',
`price` decimal(20,6) NOT NULL DEFAULT '0.000000',
`ecotax` decimal(17,6) NOT NULL DEFAULT '0.000000',
`quantity` int(10) NOT NULL DEFAULT '0',
`weight` decimal(20,6) NOT NULL DEFAULT '0.000000',
`unit_price_impact` decimal(17,2) NOT NULL DEFAULT '0.00',
`default_on` tinyint(1) unsigned NOT NULL DEFAULT '0',
`minimal_quantity` int(10) unsigned NOT NULL DEFAULT '1',
`available_date` date NOT NULL,
PRIMARY KEY (`id_product_attribute`),
KEY `product_attribute_product` (`id_product`),
KEY `reference` (`reference`),
KEY `supplier_reference` (`supplier_reference`),
KEY `product_default` (`id_product`,`default_on`),
KEY `id_product_id_product_attribute` (`id_product_attribute`,`id_product`)
) ENGINE=InnoDB AUTO_INCREMENT=418 DEFAULT CHARSET=utf8

CREATE TABLE `specific_price` (
`id_specific_price` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_specific_price_rule` int(11) unsigned NOT NULL,
`id_cart` int(11) unsigned NOT NULL,
`id_product` int(10) unsigned NOT NULL,
`id_shop` int(11) unsigned NOT NULL DEFAULT '1',
`id_shop_group` int(11) unsigned NOT NULL,
`id_currency` int(10) unsigned NOT NULL,
`id_country` int(10) unsigned NOT NULL,
`id_group` int(10) unsigned NOT NULL,
`id_customer` int(10) unsigned NOT NULL,
`id_product_attribute` int(10) unsigned NOT NULL,
`price` decimal(20,6) NOT NULL,
`from_quantity` mediumint(8) unsigned NOT NULL,
`reduction` decimal(20,6) NOT NULL,
`reduction_type` enum('amount','percentage') NOT NULL,
`from` datetime NOT NULL,
`to` datetime NOT NULL,
PRIMARY KEY (`id_specific_price`),
KEY `id_product` (`id_product`,`id_shop`,`id_currency`,`id_country` ,`id_group`,`id_customer`,`from_quantity`,`from`,` to`),
KEY `from_quantity` (`from_quantity`),
KEY `id_specific_price_rule` (`id_specific_price_rule`),
KEY `id_cart` (`id_cart`)
) ENGINE=InnoDB AUTO_INCREMENT=1433 DEFAULT CHARSET=utf8

CREATE TABLE `stock_available` (
`id_stock_available` int(11) unsigned NOT NULL AUTO_INCREMENT,
`id_product` int(11) unsigned NOT NULL,
`id_product_attribute` int(11) unsigned NOT NULL,
`id_shop` int(11) unsigned NOT NULL,
`id_shop_group` int(11) unsigned NOT NULL,
`quantity` int(10) NOT NULL DEFAULT '0',
`depends_on_stock` tinyint(1) unsigned NOT NULL DEFAULT '0',
`out_of_stock` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id_stock_available`),
UNIQUE KEY `product_sqlstock` (`id_product`,`id_product_attribute`,`id_shop`),
KEY `id_shop` (`id_shop`),
KEY `id_shop_group` (`id_shop_group`),
KEY `id_product` (`id_product`),
KEY `id_product_attribute` (`id_product_attribute`)
) ENGINE=InnoDB AUTO_INCREMENT=2784 DEFAULT CHARSET=utf8

Hopefully that's sufficient for someone to help.

Thanks,

Ron

Anyone have any ideas?

This is driving me nuts.