subqueries

[Guest]

I am trying to create a subquery using where not exists but seem to be
getting unexpected results in Access. Here is what I am trying to do. Trying
to get results from tableA if they are not in tableB

Select [TableA].NHA, [TableA].Part
From TableA where not exists (Select * from TableB where TabeB.NHA =
TableA.NHA and TableB.Part = TableA.Part)

Access seems to convert this to some other query. Am I not doing something
right here?

 

[Tom Ellison]

Dear Dave:

Your query looks alright. Pretty standard stuff.

Now, what "unexpected results" do you see? Do you see rows that shouldn't
exist, because both NHA and Part are also in TableB? Or are you missing
some rows where NHA and Part are in TableA and are not in Table B?

Now, this is going to exclude only rows where the pair of values NHA and
Part are in TableB, not where either NHA or Part are in TableB, but only
where they exist together in a single row. Is that what you intend?

When Access "mangles" this, what does it look like?

Tom Ellison

 

[Guest]

Thanks for the reply.

I do not have the query in front of me right now but access converts the
query to something like this:

Select [TableA].NHA, [TableA].Part
From TableA, TableB where exists ((Select * from TableB where TabeB.NHA =
TableA.NHA and TableB.Part = TableA.Part)=False)

I want to eliminate all criteria meeting NHA and Part as equal in both
tables per row and return results from tableA that do not exists in TableB

Dear Dave:

Your query looks alright. Pretty standard stuff.

Now, what "unexpected results" do you see? Do you see rows that shouldn't
exist, because both NHA and Part are also in TableB? Or are you missing
some rows where NHA and Part are in TableA and are not in Table B?

Now, this is going to exclude only rows where the pair of values NHA and
Part are in TableB, not where either NHA or Part are in TableB, but only
where they exist together in a single row. Is that what you intend?

When Access "mangles" this, what does it look like?

Tom Ellison

I am trying to create a subquery using where not exists but seem to be
getting unexpected results in Access. Here is what I am trying to do.
Trying
to get results from tableA if they are not in tableB

Select [TableA].NHA, [TableA].Part
From TableA where not exists (Select * from TableB where TabeB.NHA =
TableA.NHA and TableB.Part = TableA.Part)

Access seems to convert this to some other query. Am I not doing
something
right here?

 

[Tom Ellison]

Dear Dave:

For a mangling, that's not half bad. It should work the same.

So, you did not say yet what is wrong with the results you see. Get back
with that when you can, OK:?

Tom Ellison

Thanks for the reply.

I do not have the query in front of me right now but access converts the
query to something like this:

Select [TableA].NHA, [TableA].Part
From TableA, TableB where exists ((Select * from TableB where TabeB.NHA =
TableA.NHA and TableB.Part = TableA.Part)=False)

I want to eliminate all criteria meeting NHA and Part as equal in both
tables per row and return results from tableA that do not exists in TableB

Dear Dave:

Your query looks alright. Pretty standard stuff.

Now, what "unexpected results" do you see? Do you see rows that
shouldn't
exist, because both NHA and Part are also in TableB? Or are you missing
some rows where NHA and Part are in TableA and are not in Table B?

Now, this is going to exclude only rows where the pair of values NHA and
Part are in TableB, not where either NHA or Part are in TableB, but only
where they exist together in a single row. Is that what you intend?

When Access "mangles" this, what does it look like?

Tom Ellison

I am trying to create a subquery using where not exists but seem to be
getting unexpected results in Access. Here is what I am trying to do.
Trying
to get results from tableA if they are not in tableB

Select [TableA].NHA, [TableA].Part
From TableA where not exists (Select * from TableB where TabeB.NHA =
TableA.NHA and TableB.Part = TableA.Part)

Access seems to convert this to some other query. Am I not doing
something
right here?