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

I can't seem to produce a valid MySQL (version MySQL 5.6). I have the bellow SQL that works well but it returns records only when all conditions are satisfied. I need it to return records for all users having the missing data as NULL. So LEFT JOINS would be the way (unless there is a better way). Please note about the usage of one of the tables twice.

我似乎无法生成有效的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;

That produces:

这会产生：

array(size = 2)
  0 =>
    array(size = 8)
      'email' => string '[e-mail]'
      '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 '[e-mail]'
      'first_name' => string 'Peter'
      'last_name' => string 'Smith'
      'country' => string 'Spain'
      'interest' => string 'Swimming'
      'actions' => string '44'
      'badges' => string '5'
      'points' => string '212.00'

And here are the CREATE TABLE statements of all tables involved:

以下是所涉及的所有表的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;

I would be very thankful for your help.

我非常感谢你的帮助。

1 个解决方案

#1

You already gave the right hint with LEFT JOIN yourself. So, it could be as easy as

你自己已经给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 
      ;

Please note, however, it is currently unclear, whether this statement is really what you want (to LEFT JOIN in all cases). There might be cases, where an INNER JOIN is more appropriate. To check, we would need to discuss about foreign key relationship and/or have the CREATE TABLE statements of all the tables involved.

但请注意，目前还不清楚这个陈述是否真的是你想要的（在所有情况下都是LEFT JOIN）。可能存在INNER JOIN更合适的情况。要检查，我们需要讨论外键关系和/或具有所涉及的所有表的CREATE TABLE语句。