我有3个表,位置,查找并排除以下信息...

位置表

+----+--------------+
| id | location     |
+----+--------------+
| 1  | Location a   |
| 2  | Location b   |
| 3  | Location c   |
| 5  | Location d   |
| 6  | Location e   |
| 7  | Location f   |
| 8  | Location g   |
| 9  | Location h   |
+----+--------------+


查找表

+----+-------------+------+
| id | location_id | code |
+----+-------------+------+
| 1  | 2           | PR6  |
| 2  | 2           | PR7  |
| 3  | 2           | PR9  |
| 4  | 5           | WA2  |
| 6  | 8           | WA3  |
+----+-------------+------+


排除表

+----+-------------+------+
| id | location_id | code |
+----+-------------+------+
| 1  | 2           | PR5  |
| 2  | 2           | PR8  |
+----+-------------+------+


这些表用于基本的邮政编码查找。我需要创建一些SQL,以便可以使用邮政编码进行搜索,但是我需要考虑所有排除的邮政编码,例如...如果我要搜索“ PR7”,我将得到“位置B”但是如果我要搜索PR5或PR8,我将不会获得“位置B”,因为这些在排除表中。这是我到目前为止所拥有的...

SELECT
lookup.*, exclude.`code` as exclude, location.location
FROM lookup
LEFT JOIN
exclude
ON lookup.location_id = exclude.location_id
LEFT JOIN
location
ON location.location = lookup.location_id
WHERE lookup.`code` LIKE 'PR%' AND (exclude.`code` NOT LIKE 'PR8%' OR ISNULL(exclude.`code`))
GROUP BY location.location
ORDER BY location.id


在上面的SQL中,用户键入了“ PR8”,该正使用PHP变量发送到where子句,我在查找表中查找邮政编码的前2个字母,然后我还要尝试查找用户输入的内容在排除表中将其从结果中忽略。不幸的是,我不能说出我要去哪里,我得到以下结果,根据上表,由于排除,我应该没有结果...

+----+-------------+------+---------+
| id | location    | code | exclude |
+----+-------------+------+---------+
| 1  | Location b  | PR8  | PR5     |
+----+-------------+------+---------+


我希望我已经解释得足够好了,但是请告诉我是否需要添加更多细节。

非常感谢您提供一些指导,在此先感谢:)

最佳答案

我认为您使用“ NOT IN”。

因此,您的“ AND”将更像

AND lookup.`code` NOT IN (SELECT 'code' FROM exclude)


编辑
我很确定这是可行的,不确定最佳性能,我会将其留给比我聪明的人。

SELECT
lookup.*, location.location
FROM lookup
LEFT JOIN
location
ON location.id = lookup.location_id
WHERE lookup.`code` LIKE 'PR%'
AND lookup.`code` NOT IN (SELECT `code` FROM exclude)
GROUP BY location.location
ORDER BY location.id

关于php - MySQL查询加入3个表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18405352/

10-11 05:42