CODEWITHSUNDEEP
sqldatabasems-access
Enyalius
Enyalius

Reputation: 309

If logic in SQL or Access queries

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?

Thanks in advance!

Upvotes: 3

Views: 762

Answers (5)

Fionnuala
Fionnuala

Reputation: 91366

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

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]

Upvotes: 5

RolandTumble
RolandTumble

Reputation: 4703

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.

Upvotes: 0

El Yobo
El Yobo

Reputation: 14956

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 :)

Upvotes: 0

El Yobo
El Yobo

Reputation: 14956

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

Upvotes: -1

Related Questions