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?
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));
0
hnasrCommented:
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
0
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
yes
0
dbbishopCommented:
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

0
vbnetcoderAuthor Commented:
dbbishop,

Is there a difference between this logic and what rey posted as far as the result?
0
dbbishopCommented:
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.
0
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?
0
dbbishopCommented:
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
0
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!
0
Rey Obrero (Capricorn1)Commented:
@vbnetcoder,

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

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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.