If the logic in SQL or Access queries

Question

Greetings, friendly people.

I am working on a project in which I am working with an Access database. Here's the setting:

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'm trying to accomplish is this: I would like to have a way to put "Unknown" in the "Command" field if the identifiers do not match somewhere along the line. Any ideas?

Thanks in advance!

Answer 1

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

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

Answer 3

, Access SQL case, . , SQL- .

-, switch ; . , .

, , :)

Answer 4

, , CASE, Nz - , , , , , :

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]

Iif (, trueAnswer, falseAnswer) , Nz, - IsNull, .

Answer 5

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

The example shown here is fairly standard.

Wow, do access requests really look like this? Hmm ... Then maybe 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];