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"
<paulquinlan...@hotmail.comwrote: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 <rrossk...@sbcglobal.netwrote:

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

On Nov 7, 9:15 am, "paulquinlan...@hotmail.com"
<paulquinlan...@hotmail.comwrote: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:You are only selecting records from the one table: SELECT
qryBlackbook.*
One of the joins should select records from the other table.