如何从多个表中选择和匹配？

This is my table layout:

这是我的表格布局:

-- Table structure for table `areas`
CREATE TABLE IF NOT EXISTS `areas` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `country` varchar(20) NOT NULL,
  `city` varchar(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Table structure for table `matches`
CREATE TABLE IF NOT EXISTS `matches` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `view_id` bigint(20) unsigned NOT NULL,
  `status` enum('h','n') NOT NULL,
  `exp_date` date NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Table structure for table `users`
CREATE TABLE IF NOT EXISTS `users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `limit_age` varchar(5) NOT NULL DEFAULT '18:30',
  `limit_gender` varchar(2) DEFAULT NULL,
  `notifications` int(11) NOT NULL DEFAULT '0',
  `name` varchar(30) NOT NULL,
  `email` varchar(40) NOT NULL,
  `image_big` varchar(120) NOT NULL,
  `image_small` varchar(120) NOT NULL,
  `crop_data` int(11) DEFAULT NULL,
  `visible` tinyint(1) NOT NULL DEFAULT '0',
  `age` int(11) DEFAULT NULL,
  `registered_at` datetime NOT NULL,
  `views` bigint(20) unsigned NOT NULL DEFAULT '0',
  `hots` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

I will try to explain this better:

我会尝试更好地解释这个:

I have a given ID. I would like to select one entry from users which is not the ID i have given AND which user_id does not exist in matches AND has visible = 1 AND where any country + city matches the given users country + city

我有一个给定的ID。我想从用户中选择一个不是我给出的ID以及哪个user_id在比赛中不存在并且可见= 1并且其中任何国家/城市与给定用户国家/城市匹配的条目

Is this the correct way to do it (12 is an example of an given ID):

这是正确的方法(12是给定ID的示例):

SELECT *
FROM users a
INNER JOIN areas ON areas.user_id = a.id
WHERE a.id NOT IN (SELECT user_id FROM matches)
AND NOT a.id = '12'
AND a.limit_age = '18:30'
AND a.visible = '1'
AND areas.country = 'sverige'
AND areas.city = 'gbg'

Sorry for the confusion :)

对困惑感到抱歉 :)

1 个解决方案

#1

Ok, I'll make an attempt at this:

好的,我会尝试这个:

SELECT *
FROM users a
INNER JOIN areas ON areas.user_id = a.id
WHERE a.id NOT IN (SELECT user_id FROM matches)
AND a.visible = '1'
AND a.limit_age = '18:30'
AND a.limit_gender = 'f'
AND areas.country = ?
AND areas.city = ?;

This is SELECTing from "users", and returning a result only if that user also has an entry in the "areas" table. The first item in the WHERE clause ensures that a row will not be returned if the users.id (a.id) is found in the user_id field on the "matches" table. Next, I added checks for visible = 1, limit_age, and limit_gender as specified in his attempt. Finally, I left country and city parameterized so that they can be added as parameters in the php code. If anything that should give you a starting point.

这是从“用户”中选择,并且只有当该用户在“区域”表中也有一个条目时才返回结果。 WHERE子句中的第一项确保如果在“matches”表的user_id字段中找到users.id(a.id),则不会返回行。接下来,我在他的尝试中添加了对visible = 1,limit_age和limit_gender的检查。最后,我将country和city参数化,以便可以将它们作为参数添加到php代码中。如果有什么应该给你一个起点。