mysql - SQL 查询 : Join causes triplicate result

Question

编程使用 mySQL (phpMyAdmin) 和 MySQL Workbench 的 SQL
技术水平 无话可说
注意/PREFIX/附带了我在 18 个月前约定要编写的 SQL。

你好，
我正在尝试从我的 SQL 数据库中检索国家/地区、CountryID 和州。
不幸的是，由于国家在(订单 T5)中，因此需要使用第二个连接； CountryID & State in (address_book T7).

根据我的阅读，可能是连接太多了？ (我有点困惑)。

导致问题的连接(我试过:内连接、左连接、右连接):

left Join /*PREFIX*/address_book T7 On (T7.customers_id = T5.customers_id)

完整的 FROM 子句:

From /*PREFIX*/products T1
    Left Join /*PREFIX*/orders_products T2 On (T1.products_id = T2.products_id)
    Inner Join /*PREFIX*/orders T5 On (T5.orders_id = T2.orders_id)
    Inner Join /*PREFIX*/products_description T3 On (T1.products_id = T3.products_id)
    Inner Join /*PREFIX*/customers T6 On (T5.customers_id = T6.customers_id)
    **left Join /*PREFIX*/address_book T7 On (T7.customers_id = T5.customers_id)**
    Left Join /*PREFIX*/paypal P1 On (P1.order_id = T5.orders_id)
    Left Join /*PREFIX*/manufacturers T4 On (T1.manufacturers_id = T4.manufacturers_id)
    Where (T5.date_purchased >= 20120101) 
    And (T5.date_purchased <= 20140227) 
    And T5.orders_status = 1

查询包含所有店铺订单信息

Select
-- Other store Query --

-- Customer Information --
    T5.customers_id As CID,
    T6.customers_firstname As CFirst,
    T6.customers_lastname As CLast,
    T5.customers_name As CName,
    T5.customers_email_address As CEmail,
    T5.customers_country As CCountry,

    -- Following 2 lines from the 'FROM'
    T7.entry_country_id As CCountryID,
    T7.entry_state As CState,

我知道它可以做一些整理，但我只是 SQL 的初学者，足以解决少量代码问题，然后将其粘贴回一起。

使用复制/创建语句

CREATE TABLE `address_book` (
  `address_book_id` int(11) NOT NULL AUTO_INCREMENT,
  `customers_id` int(11) NOT NULL DEFAULT '0',
  `entry_gender` char(1) NOT NULL DEFAULT '',
  `entry_company` varchar(64) DEFAULT NULL,
  `entry_firstname` varchar(32) NOT NULL DEFAULT '',
  `entry_lastname` varchar(32) NOT NULL DEFAULT '',
  `entry_street_address` varchar(64) NOT NULL DEFAULT '',
  `entry_suburb` varchar(32) DEFAULT NULL,
  `entry_postcode` varchar(10) NOT NULL DEFAULT '',
  `entry_city` varchar(32) NOT NULL DEFAULT '',
  `entry_state` varchar(32) DEFAULT NULL,
  `entry_country_id` int(11) NOT NULL DEFAULT '0',
  `entry_zone_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`address_book_id`),
  KEY `idx_address_book_customers_id_zen` (`customers_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2917 DEFAULT CHARSET=utf8;

Answer 1

客户可能有多个地址，这会导致结果倍增。您应该尝试阅读 LEFT JOIN 的工作原理