请帮助我理解为什么MySQL调用无效

Ultimately, my end goal is to provide a list of possible selections to a consumer based on his/her home state. I want this to be handled entirely through MySQL as we are trying as much as possible to create a truly dynamic site. If a member's state is not an issue, display all available actions. If a member lives in a 'issue' state, do not display that option.

最后，我的最终目标是根据消费者的家乡州向他们提供可能的选择列表。我希望完全通过MySQL来处理，因为我们正在尽可能地创建一个真正动态的站点。如果成员的状态不是问题，则显示所有可用的操作。如果成员处于“问题”状态，不要显示该选项。

I'll go ahead and start by listing example tables for my current issue:

我将首先列出当前问题的示例表:

Table_A

ID  |     Name        |   value
-------------------------------
1   |       f         |   5.99
2   |       g         |   4.25
3   |       h         |   3.99

Table_B

ID   |    state
---------------
2    |      NC

For a consumer coming from Arizona, his results would show:

对于来自亚利桑那州的消费者来说，他的研究结果表明:

1 - f - 5.99
2 - g - 4.25 
3 - h - 3.99

whereas from North Carolina (NC) it would be:

而北卡罗来纳州(NC)则是:

1 - f - 5.99 
3 - h - 3.99

because option 2 is not valid to him/her.

因为选项2对他/她无效。

I believe the issue CAN be solved with a WHERE NOT EXIST but I would much prefer a join to accomplish the goal.

我相信这个问题可以用一个不存在的地方来解决，但是我更喜欢加入来实现这个目标。

My initial query is:

我的初始查询的方法是:

SELECT a.name, a.value FROM Table_A a
JOIN Table_B b ON a.ID = b.ID WHERE
b.state != 'NC'

this does not work however

然而，这并不奏效

I THOUGHT I could also put the WHERE statement within the ON clause:

我想我也可以把WHERE语句放在ON子句中:

SELECT a.name, a.value FROM Table_A a
JOIN Table_B b ON (a.ID = b.ID AND b.state!='NC')

but this does not work either.

但这也不管用。

While I am happy reaching my goal, it is more important for me to understand why the JOIN I mention is not working.

虽然我很高兴实现了我的目标，但对我来说更重要的是理解为什么我提到的加入无效。

3 个解决方案

#1

This might be a bit cleaner:

这可能更简洁一些:

SELECT 
  a.name, 
  a.value 
FROM Table_A a 
  LEFT OUTER JOIN Table_B b ON a.ID = b.ID AND b.state = 'NC'
WHERE b.ID is null

#2

Try this:

试试这个:

SELECT * FROM Table_A a
LEFT OUTER JOIN (SELECT * FROM Table_B WHERE state = 'NC') b ON a.id = b.id
WHERE b.state IS NULL

I don't have access to my MySQL box at the moment so I can't test it, but I think it will work. If not, I'll test it when I get home in about 30 minutes.

我现在没有访问MySQL的权限，所以我无法测试它，但我认为它会起作用。如果没有的话，我会在大约30分钟后回家测试。

#3

1- you should LEFT JOIN to get all result of table_a for which table_b.id = table_a.id OR table_b.id is null 2- in order to exclude "issue" state, only select from the join where state is not set, or set to a state different from the user's

1-您应该离开JOIN，以得到table_b的所有结果。id = table_a。id或table_b。id为空2——为了排除“issue”状态，只从未设置状态的join中选择，或者设置为与用户不同的状态。

So the query is:

所以查询的方法是:

SELECT a.name, a.value FROM Table_A a LEFT JOIN Table_B b ON a.ID = b.ID 
WHERE b.state is null OR b.state != '$user_state';

#1