If logic in SQL or Access queries

Greetings, fellow SO people.

I am working on a projec开发者_StackOverflowt 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?

Thanks in advance!

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]

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];

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.

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.

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

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:

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.

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.

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];