Help with a join

Question

Post reply

Help with a join

Viewing 15 posts - 16 through 30 (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/20/2010)
awesome.. it's actually all coming together in my head 🙂

The legendary Linda Lovelace apparently said something similar.

The Y is a literal, and as such requires to be bound by single quotes, like this:

= 'Y'

^{“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

SELECT a.Contact_info

,b.MEMBER_NUMBER

,c.CLIENT_NUMBER

,a.CONTACT_TYPE_ID

,d.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 b.MEMBER_NUMBER = 1980261

AND d.History_YN <> 'Y'

Blows up with this error: ??

"D.HISTORY_YN" is not valid in the context where it is used.

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

Which table contains the column HISTORY_YN?

^{“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 4

Fraternal.contacts table contains the History_YN column

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

SELECT a.Contact_info

,b.MEMBER_NUMBER

,c.CLIENT_NUMBER

,a.CONTACT_TYPE_ID

,d.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 b.MEMBER_NUMBER = 1980261

AND a1.History_YN <> 'Y'

^{“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 6

This works for displaying all rows where History = 'Y'

SELECT a.Contact_info

,b.MEMBER_NUMBER

,c.CLIENT_NUMBER

,a.CONTACT_TYPE_ID

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 = 'Y'

The History_YN column values are either Y or null. How do I display only the nulls???

AND a.History_YN <> 'Y' does not work - returns no rows

AND a.History_YN = 'null' does not work - returns no rows

AND a.History_YN = 'l' does not work - returns no rows

jcdyntek Ten Centuries Points: 1153 More actions · Answer 7

SELECT 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 b.MEMBER_NUMBER = 1980261

AND a.History_YN <> 'Y'

You are using an invalid alias of d in both the select list and the where clause

if history_yn is in the FRATERNAL.CONTACTS TABLE then change the alias of both the fields to a as above

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

AND a.History_YN IS NULL

The opposite would be:

AND NOT a.History_YN IS NULL

or

AND a.History_YN IS NOT NULL

If you want to compare your column with a second literal, then it might be:

WHERE (a.History_YN IS NULL OR a.History_YN = 'N')

^{“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

jcdyntek Ten Centuries Points: 1153 More actions · Answer 9

to display nulls and Y then use

where (a.History_YN is null or a.History_YN = 'Y')

Jpotucek SSCoach Points: 18583 More actions · Answer 10

You guys are awesome!

I have a new requirement. I need to pull in matching rows from the Client table.

the receive_email_notice column is either Y, N or Null.

I thought this would work:

SELECT 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

AND c.receive_email_notice is NOT NULL

??

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

This won't return any rows because receive_email_notice can't be NULL and NOT NULL at the same time. Something like this should do the trick:

AND (c.receive_email_notice is NULL

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

The extra pair of brackets is vital - don't lose them, otherwise your ANDs and ORs will give results which don't make sense.

^{“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 12

awesome. thank you! they syntax kills me everytime!

Jpotucek SSCoach Points: 18583 More actions · Answer 13

a little more help? 😀

here is the lastest version of my SQL:

SELECT 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'))

This query is returning Four columns: Contact_info, Memeber_number, Client_number, Contact_Type_ID and History_YN

What I am NOT getting are the values in the history_YN column. Correct number of rows but the column is all blank. I need the NULL, Y, or N values to be populated.. what am I doing wrong?

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

Jpotucek (1/27/2010)
SELECT 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'))
What I am NOT getting are the values in the history_YN column. Correct number of rows but the column is all blank. I need the NULL, Y, or N values to be populated.. what am I doing wrong?

You will only get rows where a.History_YN is NULL. I guess your client is displaying NULL as blank.

^{“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 15

so how do I get my query to actually populate History_YN with either blank, Y or N?