Joins... Urgent

  • Thread starter Thread starter Mudassir Iqbal
  • Start date Start date
M

Mudassir Iqbal

I want values from TabA which are not in TabB

Tab A
Fld A
1
2
3
4
5

Tab B
Fld A Fld B
1 65
2 65
4 65
4 66
5 66


i have tried

SELECT TabA.FldA
FROM TabA
where ((([TabA].[FldA]) not in (Select FldA from TabB
where Fldb=66)));

Ms Access XP is not supporitng the query

and

SELECT TabA.fldA
FROM TabA left join TabB on TabA.FldA = TabB.FldA
where
TabB.FldA is nUll and TabB.FldB=66;

returning Null

Pls help...
Mudassir Iqbal
 
SELECT TabA.fldA
FROM TabA left join TabB on TabA.FldA = TabB.FldA
where
TabB.FldA is nUll and TabB.FldB=66;
Click to expand...

If there is no record in TabB that matches any record in TabA, then
the value of FldB will CERTAINLY not be equal to 66 - there isn't any
record at all so it can't have a value!

If you want to find records in TabA for which there is no
corresponding record in that subset of TabB which has FldB equal to
66, use a Subquery:

SELECT TabA.*
FROM TabA
WHERE NOT EXISTS
(SELECT TabB.FldA FROM TabB
WHERE TabB.FldA = TabA.FldA
AND TabB.FldB = 66);
 
Back
Top