Selective Union

[aireq]

I have a database with two queries with similar data that I'm trying to
combine [Areas List], and [Mass Floors List]. Both of which have fields
[Level Name] and [Area].

However, I do not want to include all the values from the [Mass Floors]
query. If there are any [Area] values from [Area List] on a specific level
then I want to omit the corresponding [Area] values from [Mass Floor List] on
that level. Thus the results should only show values from either [Areas List]
or [Mass Floors List] for each [Level Name], but never both.

The union select query below works fine except for the WHERE clause
(obviously). How do I create a WHERE clause that performs the logic that I
have written out below?




SELECT [Level Name],[Area]
FROM [Areas List]

UNION ALL SELECT [Level Name],[Area]
FROM [Mass Floors List]
WHERE [Level Name] is not found anywhere in the [Area] field of the [Area
List] query

ORDER BY [Level Name]

 

[Lord Kelvan]

SELECT [Level Name],[Area]
FROM [Areas List]
UNION ALL
SELECT [Level Name],[Area]
FROM [Mass Floors List]
WHERE [Level Name] not in (
select [Level Name] from [Areas List])
ORDER BY [Level Name]

that should do it

Regards
Kelvan