如果逻辑在SQL或访问查询

Greetings, fellow SO people.

所以人们问候,研究员。

I am working on a project that has me working with an Access database. Here's the setup:

我正在做一个项目，让我使用一个访问数据库。这是设置:

I have three tables:

我有三个表:

Tab1 with employee names, ID#s, Manager names and Manager ID#s.
Tab2 with chat info, employee ID#s and employee names.
Tab3 with Manager ID#s, Manager names and team names.

I currently have a query that selects the following:

我目前有一个查询选择如下:

tab2.[employee name], tab2.[employee id], tab3.[chat info], tab1.[manager id], tab1.[manager id], tab3.[team name]
where
tab2.[employee id] = tab1.[employee id] and tab2.[manager id] = tab3.[manager id];

What I am trying to accomplish is this: I would like to have a way to put "Unknown" in the "Team" field if the IDs don't match up somewhere along the line. Any ideas?

我想要实现的是:如果id不匹配，我希望有一种方法将“Unknown”放到“Team”字段中。什么好主意吗?

Thanks in advance!

提前谢谢!

5 个解决方案

#1

Perhaps something like:

也许类似:

select tab2.[employee name], 
       tab2.[employee id], 
       tab3.[chat info], 
       tab1.[manager id], 
       Nz(tab3.[team name], 'Unknown') as [team name]
    from (tab2
        left join tab1
            on tab2.[employee id] = tab1.[employee id])
        left join tab3
            on tab2.[manager id] = tab3.[manager id]

#2

SELECT
   tab2.[employee name], tab2.[employee id], 
   tab3.[chat info], tab1.[manager id], 
   tab1.[manager id], 
   Nz(tab3.[team name],"Unknown")
FROM (tab2 
LEFT JOIN tab1 
ON tab2.[employee id] = tab1.[employee id]) 
LEFT JOIN tab3 
ON tab2.[manager id] = tab3.[manager id];

#3

It seems that Access doesn't support SQL case statements, so my previous answer is incorrect. I'm leaving it there for anyone looking for the same problem with a SQL compliant database.

看来Access不支持SQL case语句，所以我之前的回答是不正确的。我把它留给那些在SQL兼容数据库中寻找同样问题的人。

Apparently you can use a switch statement to achieve the same result; this example shows how a switch can be used. Hopefully that's more helpful.

显然，您可以使用switch语句来实现相同的结果;这个例子展示了如何使用开关。希望这更有帮助。

Otherwise, if possible, switch to a real DB :)

否则，如果可能的话，切换到一个真正的DB:)

#4

Okay, I was slow off the mark with the fact that you can't use CASE, and Joe's use of Nz is probably your best bet, but there is another Access-specific alternative, presented here in case it fits your situation better:

好吧，我说得有点慢了，因为你不能用CASE，而且Joe使用Nz可能是你最好的选择，但是这里有另一种特定于access的选择，以防它更适合你的情况:

select tab2.[employee name],
       tab2.[employee id],
       tab3.[chat info],
       tab1.[manager id],
       Iif(IsNull(tab3.[team name]), 'Unknown', tab3.[team name]) as [team name]
    from tab2
        left join tab1
            on tab2.[employee id] = tab1.[employee id]
        left join tab3
            on tab2.[manager id] = tab3.[manager id]

In this situation, Iif( condition, trueAnswer, falseAnswer ) does the same thing as the Nz, but if your condition is something besides an IsNull it can be more flexible.

在这种情况下，Iif(condition, trueAnswer, falseAnswer)和Nz做的事情是一样的，但是如果您的条件是除IsNull之外的其他东西，那么它可以更灵活。

#5

-1

I'm not sure about Access, but in SQL normally you would left join your tables and you can use a CASE statement to add the conditional behaviour your looking for; access may not support this.

我不确定访问，但是在SQL中通常你会留下连接你的表你可以使用CASE语句来添加你想要的条件行为;访问可能不支持这一点。

The example shown here is reasonably standard.

这里显示的示例是合理的标准。

Wow, do access queries really look like that? Hmmm... Then perhaps something like this.

哇，访问查询真的是这样的吗?嗯…也许是这样。

tab2.[employee name], tab2.[employee id], tab3.[chat info], tab1.[manager id], tab1.[manager id], case when tab3.[team name] is null then 'Unknown' else tab3.[team name] end as [team name]
where
tab2.[employee id] = tab1.[employee id] and tab2.[manager id] = tab3.[manager id];

#1