SQL search for records that do and do not exist in another table
Posted on 2014-03-18
I have a fairly complex schema in Access 2007 that does a join on several tables based on primary / foreign keys.
The problem I am having is that queries I have created only contain records where both values exist in both tables....I need to include these, but also records where values don't exist in both tables.
Join Table A with Table B, on VesselID.
Table A contains: LocationID (PK), LocationName, VesselID(FK)
Table B contains: VesselID(PK), VesselName
I want to join Table A & B, but want to display all records in Table A that have a Vessel assigned to them, but also records that do not have a vessel assigned.
Right now, I'm doing an INNER JOIN, which shows all records in Table A that have references to Table B based on VesselID. If I do a LEFT JOIN, I would get all records in Table A that do not have records in Table B, but I need to do both.
Am I missing something simple here?
My real query is much more complex, and involves several tables, but I'm trying to "dumb it down" here.
Any help would be appreciated.