Help with a join

Question

Help with a join

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Jpotucek (1/27/2010)
so how do I get my query to actually populate History_YN with either blank, Y or N?

Take it out of the WHERE clause. You must have had it in there for a good reason at some point, so I suggest you comment it out (with two adjacent minus characters), like this:

...

WHERE a.CONTACT_TYPE_ID in ('Email1')

--AND a.History_YN is NULL

AND (c.receive_email_notice is NULL ...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jpotucek SSCoach Points: 18583 More actions · Answer 2

That worked!!!!! Thank you so much.

Now I am trying to exclude duplicates. I thought this would work.. but I seem to be getting garbage in the Client_Info column????

SELECT DISTINCT a.Contact_info

,b.MEMBER_NUMBER

,c.CLIENT_NUMBER

,a.CONTACT_TYPE_ID

,a.history_YN

FROM FRATERNAL.CONTACTS a

inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID

inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID

inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID

WHERE a.CONTACT_TYPE_ID in ('Email1')

--AND a.History_YN is NULL

AND (c.receive_email_notice is NULL

OR c.receive_email_notice IN ('Y', 'N'))

BenWard SSCertifiable Points: 5903 More actions · Answer 3

Ok....

Perhaps your database actually does have garbage in the contact_info column? Lots of people type junk into an email address column because they dont want to receive marketting info.

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Jpotucek SSCoach Points: 18583 More actions · Answer 4

very true.. but when I add DISTINCT to my Select, I didn't expect it to totally reorder my results. Adding DISTINCT dropped 20 duplicates.

Thanks for all your help!