Use switch and "in" in MS-Access Select Query-CodePudding

let's say I have two tables, where the first one contains my data:

Automatic	Manual
X1	X1
Y	Y27

and a lookup table

Lookup
X1
Y27
...

I want to select the value from the first table, IF it is contained in the lookup table. So for the second row, it should take the value Y27, because Y ist not in the lookup table.

I tried it with this query:

Select 
Switch(
       Automatic in (SELECT Lookup FROM LookupTable), Automatic,
       True, Manual,
      ) AS ValidEntry
FROM Datatable;

My query as text: Check if the value from "Automatic" is inside the lookup table, if yes -> take the value, if not, take the value from the other column "manual" and return an entry named "ValidEntry".

CodePudding user response：

Use a INNER JOIN, it should be much faster.

select somedata from table1 t  
inner join lookupTbl l on l.lookupfield = t.automatic

With IN clause:

select somedata from table1 t  
WHERE t.automatic IN (select lookupfield FROM lookupTbl)

CodePudding user response：

This is what you need to do:

SELECT NZ( LookupTableAutomatic.Lookup, LookupTableManual.Lookup) AS Lookup
FROM (LookupTable AS LookupTableManual INNER JOIN DataTable  
ON LookupTableManual.Lookup = DataTable.Manual) 
LEFT OUTER JOIN LookupTable AS LookupTableAutomatic ON LookupTableAutomatic.Lookup = DataTable.Automatic;

By way of explanation the NZ Function returns the first parameter if it is not null, otherwise it takes the second. The lookup table is joined twice: the Manual Lookup is an INNER JOIN, as I am assuming that there will always be a fallback value, whereas the Automatic lookup is joined as an OUTER JOIN, meaning that it returns NULL when the value is not matched.