MYSQL 多表左连接条件和重复表

Question

我似乎无法生成有效的 MySQL(MySQL 5.6 版本)。我有运行良好的波纹管 SQL，但它仅在满足所有条件时才返回记录。我需要它为所有丢失数据为 NULL 的用户返回记录。所以 LEFT JOINS 将是方法(除非有更好的方法)。请注意其中一张表的用法两次。

SELECT
      users.email,
      ufs.first_name,
      ufs.last_name,
      country_label.label as country,
      interest_label.label as interest,
      points.actions,
      points.badges,
      points.points

      FROM 
      site_users users,
      site_user_fields_values interest,
      site_user_fields_options interest_label,
      site_user_fields_values country,
      site_user_fields_options country_label,
      points_users points,
      site_user_fields_search ufs

      WHERE
      interest.field_id = 15 AND
      country.field_id = 16 AND
      users.user_id = interest.item_id AND
      interest.value = interest_label.option_id AND
      users.user_id = country.item_id AND
      country.value = country_label.option_id AND
      users.user_id = points.ref_id AND
      users.user_id = ufs.item_id;

产生:

array(size = 2)
  0 =>
    array(size = 8)
      'email' => string 'info@mygreatsite.com'
      'first_name' => string 'Filip'
      'last_name' => string 'Moore'
      'country' => string 'United Kingdom'
      'interest' => string 'Fishing'
      'actions' => string '53'
      'badges' => string '4'
      'points' => string '21.00'
  1 =>
    array(size = 8)
      'email' => string 'user@mygreatsite.com'
      'first_name' => string 'Peter'
      'last_name' => string 'Smith'
      'country' => string 'Spain'
      'interest' => string 'Swimming'
      'actions' => string '44'
      'badges' => string '5'
      'points' => string '212.00'

下面是所有涉及的表的 CREATE TABLE 语句:

CREATE TABLE `site_users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `username` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `displayname` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `photo_id` int(11) unsigned NOT NULL DEFAULT '0',
  `password` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `salt` char(64) COLLATE utf8_unicode_ci NOT NULL,
  `locale` varchar(16) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'auto',
  `language` varchar(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'en_US',
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `EMAIL` (`email`),
  UNIQUE KEY `USERNAME` (`username`),
  KEY `CREATION_DATE` (`creation_date`),
  KEY `enabled` (`enabled`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `site_user_fields_options` (
  `option_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `field_id` int(11) unsigned NOT NULL,
  `label` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `order` smallint(6) NOT NULL DEFAULT '999',
  PRIMARY KEY (`option_id`),
  KEY `field_id` (`field_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `site_user_fields_values` (
  `item_id` int(11) unsigned NOT NULL,
  `field_id` int(11) unsigned NOT NULL,
  `index` smallint(3) unsigned NOT NULL DEFAULT '0',
  `value` text COLLATE utf8_unicode_ci NOT NULL,
  `privacy` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`item_id`,`field_id`,`index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `site_user_fields_search` (
  `item_id` int(11) unsigned NOT NULL,
  `profile_type` smallint(11) unsigned DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `gender` smallint(6) unsigned DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `field_15` enum('4','5','6','7','8','9','10') COLLATE utf8_unicode_ci DEFAULT NULL,
  `field_16` enum('11','12','13') COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`item_id`),
  KEY `profile_type` (`profile_type`),
  KEY `first_name` (`first_name`),
  KEY `last_name` (`last_name`),
  KEY `gender` (`gender`),
  KEY `birthdate` (`birthdate`),
  KEY `field_15` (`field_15`),
  KEY `field_16` (`field_16`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `points_users` (
  `user_id` int(11) unsigned NOT NULL auto_increment,
  `ref_id` int(11) unsigned NOT NULL,
  `actions` int(11) unsigned DEFAULT 0,
  `badges` int(11) unsigned DEFAULT 0,
  `points` FLOAT(12) unsigned DEFAULT 0,
  `creation_date` DATETIME DEFAULT '00-00-00 00:00:00',
  `modified_date` DATETIME DEFAULT '00-00-00 00:00:00',

  PRIMARY KEY  (`user_id`),
  KEY `ref_id` (`ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

非常感谢您的帮助。

Answer 1

您自己已经通过 LEFT JOIN 给出了正确的提示。所以，它可以像

SELECT
      users.email,
      ufs.first_name,
      ufs.last_name,
      country_label.label as country,
      interest_label.label as interest,
      points.actions,
      points.badges,
      points.points

      FROM site_users users 
      LEFT JOIN site_user_fields_values interest ON users.user_id = interest.item_id
      LEFT JOIN site_user_fields_options interest_label ON interest.value = interest_label.option_id
      LEFT JOIN site_user_fields_values country ON users.user_id = country.item_id
      LEFT JOIN site_user_fields_options country_label ON country.value = country_label.option_id
      LEFT JOIN points_users points ON users.user_id = points.ref_id
      LEFT JOIN site_user_fields_search ufs ON users.user_id = ufs.item_id

      WHERE
      interest.field_id = 15 AND
      country.field_id = 16 
      ;

但是请注意，目前还不清楚这个语句是否真的是您想要的(在所有情况下都是LEFT JOIN)。在某些情况下，INNER JOIN 更合适。要进行检查，我们需要讨论外键关系和/或对所有涉及的表执行 CREATE TABLE 语句。