Join with 2 matching fields

  • Thread starter Thread starter Andrew Black (delete obvious bit)
  • Start date Start date
A

Andrew Black (delete obvious bit)

I have the following query joining two tables (with 2 matching fields). So
far so good :

SELECT tblCatMaps.SeriesId, tblCatMaps.Number
FROM tblCatMaps INNER JOIN Item ON (tblCatMaps.Number = Item.Number) AND
(tblCatMaps.SeriesId = Item.SeriesId);

I want to make this an outer join, so that unmatched records in tblCatMaps
are allow included. I get the dreaded Ambiguous outer join.
How do I get what I want

THanks

Andrew
 
Andrew,

You should be able to just rewrite the SQL as:

SELECT tblCatMaps.SeriesId, tblCatMaps.Number
FROM tblCatMaps
LEFT JOIN Item
ON (tblCatMaps.Number = Item.Number)
AND (tblCatMaps.SeriesId = Item.SeriesId);

If you were doing the join in the query grid, you have to
make sure that both of the join lines are left oriented.

HTH
Dale
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Since you're only showing values from tblCatMaps, this means you're
using table Item as a "filter" that eliminates all rows from tblCatMaps
that don't have matching columns in table Item. This begs the question,
why do you want to show unmatched records in tblCatMaps when you've set
it up to do exactly the opposite? Are you showing the complete query?

The query that will show all rows from tblCatMaps is:

SELECT tblCatMaps.SeriesId, tblCatMaps.Number
FROM tblCatMaps

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQMdm7IechKqOuFEgEQJHqACfeorvzX+VbJug24jB3+N1KeUL+5MAoM8x
w0Omsu3Aku3l6odrPV5mrwUZ
=Cj+j
-----END PGP SIGNATURE-----
 
Back
Top