Grouping needed in unmatched query 1

[projecttoday]

I need to retrieve all UNmatched records in tblLevels for each distinct name (nam) in tblNames. An unmatched query will only return records that don't have a match for anybody. In otherwords, I need to group on nam. Anyone know how?

tblLevels

levelno
1
2
3
4


tblNames

level ; nam
1; Betty
2; Betty
1; Gina


results should look like:

levelno; nam
3; Betty
4; Betty
2; Gina
3; Gina
4; Gina

 

[PHV]

SELECT D.levelno, D.nam
FROM (SELECT DISTINCT L.levelno, N.nam FROM tblLevels L, tblNames N) D
LEFT JOIN tblNames X ON D.levelno = X.level AND D.nam = X.nam
WHERE X.level Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886