Results 1 to 8 of 8
  1. #1
    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

  2. #2
    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

  3. #3
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    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.

  5. #5
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  7. #7
    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

  8. #8
    Join Date
    Oct 2012
    Posts
    17
    Anyone have any ideas?

    This is driving me nuts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •