t-sql where statement

Question

t-sql where statement

wendy elizabeth

SSCoach

Points: 16915
More actions
February 5, 2014 at 2:33 pm

#287985

In t-sql 2008 r2, I am trying to determine how to setup the sql to accomplish the following
goal:
select table1.customer_id,type,start_date,end_date,Program_id from table1
join table2 on table1.customer_id = table2.customer_id
1. where type not= ('aa','cc') and type not = 'g2' where code = 3
In table1 there are lots of records for each customer_id and there can be
lots of various values for type. I only want the customer_ids that do not contain
the values listed above.
and
2. table2 has only one customer_id. Customer_id is the key of table2.
I want customers that do not have a value in one of the 3 columns:
start_date, end_date, and program_id.
Both parts 1 and 2 listed above need to be true for the customer_id to be selected.
Thus can you tell me how to setup that sql?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

First please read the article in my signature about posting these types of questions to the forum. After you do that I could make sure that the answer is correct. Without the necessary information the following where statement should get you what you want:where type not in ('aa', 'cc')

and type <> = 'g2'

and code = 3

and (table2.start_date is null

or table2.end_date is null

or table2.program_id is null)

Instead of all of the nulls you could use union all for each of the "is null" statements.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Keith Tate (2/5/2014)
First please read the article in my signature about posting these types of questions to the forum. After you do that I could make sure that the answer is correct. Without the necessary information the following where statement should get you what you want:where type not in ('aa', 'cc') and type <> = 'g2' and code = 3 and (table2.start_date is null or table2.end_date is null
or table2.program_id is null)
Instead of all of the nulls you could use union all for each of the "is null" statements.

Just a curiosity... why did you treat 'g2' separately instead of just using...

WHERE [Type] NOT IN ('aa','cc','g2')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 3

I simply glossed over that fact that it was the same column :hehe: Just went off the OP request.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Heh... Been there and done that. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Jeff Moden (2/5/2014)
Keith Tate (2/5/2014)
First please read the article in my signature about posting these types of questions to the forum. After you do that I could make sure that the answer is correct. Without the necessary information the following where statement should get you what you want:where type not in ('aa', 'cc') and type <> = 'g2' and code = 3 and (table2.start_date is null or table2.end_date is null
or table2.program_id is null)
Instead of all of the nulls you could use union all for each of the "is null" statements.
Just a curiosity... why did you treat 'g2' separately instead of just using...
WHERE [Type] NOT IN ('aa','cc','g2')

well there is that method of fixing the query. The <>= would have thrown an error 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

I saw that and figured it was just a phat phinger mistake that anyone would be able to fix and so didn't bring it up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Jeff Moden (2/5/2014)
I saw that and figured it was just a phat phinger mistake that anyone would be able to fix and so didn't bring it up.

Knowing Keith, it was most likely just a fta fingre mistake. Afterall, he knows better than that 😀

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

wendy elizabeth SSCoach Points: 16915 More actions · Answer 8

Thank you for your help! I read the post about having well formatted code. I will definitely do that the next time of having well formatted code.