Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

t-sql where statement Expand / Collapse
Author
Message
Posted Wednesday, February 5, 2014 2:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:33 AM
Points: 379, Visits: 451
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?
Post #1538381
Posted Wednesday, February 5, 2014 3:45 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:53 AM
Points: 597, Visits: 937
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
Post #1538395
Posted Wednesday, February 5, 2014 9:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 35,770, Visits: 32,440
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1538456
Posted Wednesday, February 5, 2014 9:40 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:53 AM
Points: 597, Visits: 937
I simply glossed over that fact that it was the same column 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
Post #1538459
Posted Wednesday, February 5, 2014 10:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 35,770, Visits: 32,440
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1538465
Posted Wednesday, February 5, 2014 10:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1538467
Posted Wednesday, February 5, 2014 10:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 35,770, Visits: 32,440
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1538470
Posted Wednesday, February 5, 2014 10:44 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1538471
Posted Thursday, February 6, 2014 8:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:33 AM
Points: 379, Visits: 451
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.
Post #1538693
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse