tpigielski
asked on
SQL search for records that do and do not exist in another table
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.
For example:
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.
Thanks...Tom
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.
For example:
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.
Thanks...Tom
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
e.g. SELECT Cust.Cust_Name, Tx.TX_Date FROM Cust LEFT JOIN Tx ON Cust.Cust_ID = Tx.Cust_ID;