SQL - return records where both values on join are null

Hi

I have the following query:

SELECT qryBlackbook.*
FROM qryBlackbook inner JOIN qryLatestMeetingDate ON
(qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref) AND
(qryBlackbook.MeetingDate = qryLatestMeetingDate.LatestDate);

is there a way to adjust it so that it will also return records where
both qryBlackbook.MeetingDate and qryLatestMeetingDate.LatestDate are
null?

Thanks
Paul

Re: SQL - return records where both values on join are null

On Nov 7, 8:15 am, "paulquinlan...@hotmail.com"
wrote:
> Hi
>
> I have the following query:
>
> SELECT qryBlackbook.*
> FROM qryBlackbook inner JOIN qryLatestMeetingDate ON
> (qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref) AND
> (qryBlackbook.MeetingDate = qryLatestMeetingDate.LatestDate);
>
> is there a way to adjust it so that it will also return records where
> both qryBlackbook.MeetingDate and qryLatestMeetingDate.LatestDate are
> null?
>
> Thanks
> Paul

Yes. What you want is an full outer join. Although Access doesn't
have a full outer join, it can be simulated with a union of a left
join and and a right join.

Re: SQL - return records where both values on join are null

Hi

I tried to do as you said using the query below:

SELECT qryBlackbook.*
FROM qryBlackbook LEFT JOIN qryLatestMeetingDate ON
(qryBlackbook.MeetingDate = qryLatestMeetingDate.LatestDate) AND
(qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref)
UNION
SELECT qryBlackbook.*
FROM qryBlackbook RIGHT JOIN qryLatestMeetingDate ON
(qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref) AND
(qryBlackbook.MeetingDate = qryLatestMeetingDate.LatestDate);

however, it still is not pulling through the records where both dates
are null, any ideas?

Thanks again
Paul


On 7 Nov, 14:40, OldPro wrote:
> On Nov 7, 8:15 am, "paulquinlan...@hotmail.com"
>
>
>
>
>
> wrote:
> > Hi
>
> > I have the following query:
>
> > SELECT qryBlackbook.*
> > FROM qryBlackbook inner JOIN qryLatestMeetingDate ON
> > (qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref) AND
> > (qryBlackbook.MeetingDate = qryLatestMeetingDate.LatestDate);
>
> > is there a way to adjust it so that it will also return records where
> > both qryBlackbook.MeetingDate and qryLatestMeetingDate.LatestDate are
> > null?
>
> > Thanks
> > Paul
>
> Yes. What you want is an full outer join. Although Access doesn't
> have a full outer join, it can be simulated with a union of a left
> join and and a right join.- Hide quoted text -
>
> - Show quoted text -

Re: SQL - return records where both values on join are null

On Nov 7, 9:15 am, "paulquinlan...@hotmail.com"
wrote:
> Hi
>
> I have the following query:
>
> SELECT qryBlackbook.*
> FROM qryBlackbook inner JOIN qryLatestMeetingDate ON
> (qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref) AND
> (qryBlackbook.MeetingDate = qryLatestMeetingDate.LatestDate);
>
> is there a way to adjust it so that it will also return records where
> both qryBlackbook.MeetingDate and qryLatestMeetingDate.LatestDate are
> null?
>
> Thanks
> Paul

I don't have simlar construction to test but I would try:
SELECT qryBlackbook.*
FROM qryBlackbook inner JOIN qryLatestMeetingDate ON
(qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref)
AND
(
(qryBlackbook.MeetingDate = qryLatestMeetingDate.LatestDate)
OR
(qryBlackbook.MeetingDate Is Null AND qryLatestMeetingDate.LatestDate
Is Null)
)

/*extra lines to help me get brackets right*/

I think the nature of the records returned by the two subqueries might
make this solution OK. Then again it might be useless.

Re: SQL - return records where both values on join are null

> I tried to do as you said using the query below:
>
> SELECT qryBlackbook.*
> FROM qryBlackbook LEFT JOIN qryLatestMeetingDate ON
> (qryBlackbook.MeetingDate = qryLatestMeetingDate.LatestDate) AND
> (qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref)
> UNION
> SELECT qryBlackbook.*
> FROM qryBlackbook RIGHT JOIN qryLatestMeetingDate ON
> (qryBlackbook.Site_Ref = qryLatestMeetingDate.Site_Ref) AND
> (qryBlackbook.MeetingDate = qryLatestMeetingDate.LatestDate);
>
> however, it still is not pulling through the records where both dates
> are null, any ideas?

You are only selecting records from the one table: SELECT
qryBlackbook.*
One of the joins should select records from the other table.