join syntax

How do a write a query to return records where three fields match but one field does NOT match between identical tables?
vbnetcoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you need to use a LEFT join
in the query below, it is looking for records in table "A" where the three fields col1,col2, and col3 are equal to the same fields in table "A1", but field "col4" is not

SELECT A.Col1, A.Col2, A.Col3, A.Col4, A1.Col4
FROM A LEFT JOIN A1 ON (A.Col4 = A1.Col4) AND (A.Col3 = A1.Col3) AND (A.Col2 = A1.Col2) AND (A.Col1 = A1.Col1)
WHERE (((A1.Col4) Is Null));
Hamed NasrRetired IT ProfessionalCommented:
Try
left join on 4 fields
Union
right join on 4 fields

Example:
Select 1, a.fld1, a.fld2, a.fld3, a.fld4
FROM a LEFT JOIN b on a.fld1=b.fld1 AND a.fld2=b.fld2 AND a.fld3=b.fld3 AND a.fld4=b.fld4
UNION
Select 2, a.fld1, a.fld2, a.fld3, a.fld4
FROM a RIGHT JOIN b on a.fld1=b.fld1 AND a.fld2=b.fld2 AND a.fld3=b.fld3 AND a.fld4=b.fld4

Open in new window

1 records show unmatched values, as nulls, from b table
2 records show unmatched values, as nulls, from a table
vbnetcoderAuthor Commented:
Rey Obrero,

To use your example i need to return ONLY records where only Col4 does not have a match .... col 1 --> 3 will have to match in order for it to return anything at all.  Would your query do that?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Rey Obrero (Capricorn1)Commented:
yes
Doug BishopDatabase DeveloperCommented:
I think this is what you are looking for:
select	*
from	tbl1 t1
left outer join tbl2 t2
on	t1.col1 = t2.col1
and	t1.col2 = t2.col2
and	t1.col3 = t2.col3
and	isnull(t1.col4, '') <> isnull(t2.col4, '')

Open in new window

vbnetcoderAuthor Commented:
dbbishop,

Is there a difference between this logic and what rey posted as far as the result?
Doug BishopDatabase DeveloperCommented:
I haven't run any tests, but it appears that his code would return data if any of the columns didn't match. The join condition is on all four columns matching between the tables.

If any of the four columns did not match up, a1.col4 would be NULL.
vbnetcoderAuthor Commented:
>> his code would return data if any of the columns didn't match. The join condition is on all four columns matching between the tables.

I was wondering the same.  Wouldn't your code have the same issue though?
Doug BishopDatabase DeveloperCommented:
Dang, I put together a comment, but apparently I never actually posted it, and now it is sitting in a virtual bit bucket in some virtual wasteland, never to be seen again. Let's see if I can remember what I said.

Mine needs to be changed to an inner join. It HAS to match on col1..col3 and not match on col4. It will take NULLs into consideration and if a.col4 is NULL and a1.col4 is not NULL, and the other three columns match, that should return a result.

One thing to note, if a1 has more than one row where col1..col3 match, but col4 doesn't, you will get a one-to-many match. For example, assuming the first set of 4 values is in table a and the second set in table a1:
1,2,3,4    1,2,3,4
4,3,6,8    1,2,3,3
5,8,1,0    1,2,3,7

Open in new window

the query will return 2 rows, where 1,2,3,4 in a will 'match' 1,2,3,3 and 1,2,3,7
vbnetcoderAuthor Commented:
dbbishop,

Would you please post the query the way you think it should be to make sure i am on the right page?

Thanks!
Rey Obrero (Capricorn1)Commented:
@vbnetcoder,

how about uploading a sample db with the tables.
- post here your expected result.
Doug BishopDatabase DeveloperCommented:
My query above; just change "left outer" to "inner"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
ty
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.