If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > MySQL SELECT Issues

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 17
Question MySQL SELECT Issues

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

Code:
SELECT product_attribute.reference
     , product.price
	 , specific_price.reduction
     , product.wholesale_price
	 , stock_available.quantity
	 , product_attribute.ean13
  FROM product_attribute
INNER
  JOIN product
	ON product.id_product = product_attribute.id_product
INNER
  JOIN specific_price
    ON product.id_product = specific_price.id_product
	AND product_attribute.ean13 != ''
INNER
  JOIN stock_available
    ON product.id_product = stock_available.id_product
UNION
SELECT product.reference
     , product.price
	 , specific_price.reduction
     , product.wholesale_price
	 , product.quantity
	 , product.ean13
  FROM specific_price
INNER
  JOIN product
    ON product.id_product = specific_price.id_product
	AND product.active = '1'
	AND product.ean13 != ''
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 860
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.
__________________
Ronan Cashell
Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
http://www.it-iss.com
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 17
I was afraid of that.

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

Thanks.
Reply With Quote
  #4 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,791
Quote:
Originally Posted by rseigel View Post
I'll just do the trial and error thing and I'll get it sooner or later.
...or you could get really wild and crazy and post the pk/fk information that we need to help you!

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 17
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

Last edited by rseigel; 01-22-13 at 10:03. Reason: Missed a couple of tables
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
in mysql, the best way to show DDL, including PK/FK relationships, is to run SHOW CREATE TABLE for each table, e.g.
Code:
SHOW CREATE TABLE product
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 17
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 17
Anyone have any ideas?

This is driving me nuts.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On