Union queries

[dcipollone]

I have searched this news group but I have not found an answer to my
issue so I'm hoping some can provide a solution for me.

I have 3 tables that I need to query against. I have created 2 union
queries then created another query to query against the 2 union
queries match one of the fields but not the other field and also if one
entry exists in one table but not the other.

The explanation is a bit confusing so here is the query:


SELECT [union].[ptNo], [union].[nurseStat], [union].[Bed],
[union].[AdmtDate], [union].[PtSts], [union].[Div]
FROM [union] LEFT JOIN union2 ON [union].[ptNo]=[union2].[Field1]
WHERE ([union2].[Field1]=[union].[ptNo] And
[union].[Bed]<>[union2].[Field4]) or
([union2].[Field1]<>[union].[ptNo]);


The first part of the query works fine but I cannot get the OR part of
it to work. ANy suggestions welcomed.

 

[Michel Walsh]

Hi,

SELECT [union].[ptNo], [union].[nurseStat], [union].[Bed],
[union].[AdmtDate], [union].[PtSts], [union].[Div]
FROM [union] LEFT JOIN union2 ON [union].[ptNo]=[union2].[Field1]
WHERE ([union2].[Field1]=[union].[ptNo] And
[union].[Bed]<>[union2].[Field4]) or
([union2].[Field1] IS NULL);



Note that your LEFT join introduces NULL under union2 columns if there no
record in union2 such that

[union].ptNo=union2.Field1


so you have to test for NULL, not for the equality you already used in the
JOIN.


Also, using a LEFT join means that you may miss records in union2 NOT IN
records in [union].



Hoping it may help,
Vanderghast, Access MVP

 

[dcipollone]

Thanks for the suggestion and actually understanding my gibberish in
describing the problem. I had tried using null already and you are
correct it does pull null fields. What I actually need is if the field
[union].[ptNo] is not in the union2 table in addition to the
([union2].[Field1]=[union].[ptNo] And
[union].[Bed]<>[union2].[Field4]) Fields

 

[Michel Walsh]

Hi,


True.


SELECT a.* FROM a LEFT JOIN b ON a.f1=b.f2 .... WHERE b.f2 IS NULL


will return records in a NOT IN b (more precisely, those with a.f1 not among
the values under b.f2).


The proposed solution should then work.


If not, there is probably a problem somewhere else, further on the process,
of before this query get executed, some records are (will) be dropped.



Hoping it may help,
Vanderghast, Access MVP