Multiple joins, avoiding cross joined result

[richjhart]

I would like to join three tables, using a common join column in one of the tables without it looking like cross join.

I am using Access 2003

So if I have three tables:

CommonTable:
ID Col1
-- ----
1  Item1
2  Item2

Table1
ID1 ForeignKey1 Col2
--- ----------- ----
1   1           SecondItem1
2   1           SecondItem2

Table2
ID2 ForeignKey2 Col3
--- ----------- ----
1   1           ThirdItem1
2   1           ThirdItem2

So the normal join would be:
(CommonTable INNER JOIN Table1 ON CommonTable.ID=Table1.ForeignKey) INNER JOIN Table2 ON CommonTable.ID=Table2.ForeignKey

Which results in:

ID Col1  ID1 ForeignKey1 Col2        ID2 ForeignKey2 Col3
-- ----- --- ----------- ----------- --- ----------- ----------
1  Item1 1   1           SecondItem1 1   1           ThirdItem1
1  Item1 2   1           SecondItem2 1   1           ThirdItem1
1  Item1 1   1           SecondItem1 2   1           ThirdItem2
1  Item1 2   1           SecondItem2 2   1           ThirdItem2

But what I want is to separate the joining of Table1 and Table2 to CommonTable to end up with:

ID Col1  ID1 ForeignKey1 Col2        ID2 ForeignKey2 Col3
-- ----- --- ----------- ----------- --- ----------- ----------
1  Item1 1   1           SecondItem1 NULL
1  Item1 2   1           SecondItem2 NULL
1  Item1 NULL                        1   1           ThirdItem1
1  Item1 NULL                        2   1           ThirdItem2

At first I thought outer joins would do the trick, but gradually realised that didn't help in this case.
Happy to use intermediate queries or whatever, but I just can't figure this one out

[Guigui]

To me a union query of 2 queries (one with Table1 and one with Table2) would do.

[richjhart]

Cheers. Yeah a Union did the trick. I'd forgot about them.