Why does LEFT JOIN not work in Access correctly?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

i have a big problem. I have the following table:

ID Reference MsgID
---------------------
1 3234 *
2 3234 5428
3 *
4 7478 3984 *
5 2034 9454
6 2394 2034

I want to get the Records wich are marked with *. I need the following:

All Records wich Reference is NULL OR all Records wich References and MsgID is NULL OR all Records wich Reference is not in MsgID.

How can i do that?

thanks
yavuz
 
A subquery may be useful to find something that does not exist:

SELECT ID FROM MyTable
WHERE (MyTable.Reference Is Null) OR (NOT EXISTS (
SELECT ID FROM MyTable AS Dupe
WHERE MyTable.Reference = Dupe.MsgID))

If I understood correctly, your middle condition seemed superfluous:
(Reference Is Null) AND (MsgID Is Null)
is a subset of the records where:
Reference Is Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yavuz Bogazci said:
i have a big problem. I have the following table:

ID Reference MsgID
---------------------
1 3234 *
2 3234 5428
3 *
4 7478 3984 *
5 2034 9454
6 2394 2034

I want to get the Records wich are marked with *. I need the following:

All Records wich Reference is NULL OR all Records wich References and
Click to expand...
MsgID is NULL OR all Records wich Reference is not in MsgID.
 
Back
Top