Unmatched Query with Multiple Tables

[Colleyville Alan]

I want to extract some records that are common to three tables, but not
contained in the fourth. Following what I have see in the archives and also
trying the unmatched records query wizard, if I only use one of those three
tables and use a LEFT JOIN to the fourth and set the target field in the
fourth table to null, I get the unmatched records.

However, if I link the three tables together and then want to exclude
records in the fourth table, it does not work.

What I have looks like this:

Table 1--->Table2--->Tabel3--->Table4 (is null)

Here is the code
SELECT DISTINCT Select_List_Per formance.*, Select_List.Cat egory
FROM ((Select_List_P erformance LEFT JOIN Categories ON
Select_List_Per formance.Mornin gstar_Category =
Categories.Morn ingstar_Categor y) LEFT JOIN Select_List ON
Categories.Morn ingstar_Categor y = Select_List.Cat egory) LEFT JOIN
Sorted_Template ON Select_List.Tic ker = Sorted_Template .Ticker
WHERE (((Select_List_ Performance.Cat _Bnch) Like 'c*') AND
((Categories.Se lect_List_Group ing) Like 'Large*') AND
(((Sorted_Templ ate.Ticker) Is Null)))
ORDER BY Select_List.Cat egory;

If I were to split this up into two queries, one to create a new table with
the common records of the first three tables and the second to link this to
the fourth table to get unmatched records, it works. So I am guessing that
this might be a case of not using parentheses correctly and that a subquery
would work. But I've tried that without success. Any idea as to what I am
doing wrong?

Thanks
Alan

[Christoph Sticksel]

Hi,

Colleyville Alan <ae***********@ nospam.comcast. net> wrote:

I want to extract some records that are common to three tables, but not
contained in the fourth.

I think you should use an inner join for the first three joins and an
outer join only for the last one. An outer join (i.e left or right join)
returns all records matching the key and those with a null value in the
key field. You seem to have null values in a key field for one of the
first three joins thus those records are returned, the results of the
following joins then do not matter.

HTH,
Chris

[Colleyville Alan]

"Christoph Sticksel" <sp************ ***@gmx.net> wrote in message
news:c8******** **@news2.rz.uni-karlsruhe.de...

Hi,

Colleyville Alan <ae***********@ nospam.comcast. net> wrote:

I want to extract some records that are common to three tables, but not
contained in the fourth.

I think you should use an inner join for the first three joins and an
outer join only for the last one. An outer join (i.e left or right join)
returns all records matching the key and those with a null value in the
key field. You seem to have null values in a key field for one of the
first three joins thus those records are returned, the results of the
following joins then do not matter.

HTH,
Chris

I tried that earlier and by itself it did not seem to help. But I then
tried it and also changed the order of the tables and then it worked.
Having "Categories " as the first table seems to do the trick.

FROM (Categories INNER JOIN Select_List_Per formance ON
Categories.Morn ingstar_Categor y =
Select_List_Per formance.Mornin gstar_Category) INNER JOIN (Select_List LEFT
JOIN Sorted_Template ON Select_List.Tic ker = Sorted_Template .Ticker) ON
Select_List_Per formance.Ticker = Select_List.Tic ker