Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 t-sql where clause
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 02/05/2014 :  16:31:37  Show Profile  Reply with Quote
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?

Edited by - jassie on 02/05/2014 17:10:28

sree203509
Starting Member

India
2 Posts

Posted - 02/06/2014 :  00:24:05  Show Profile  Reply with Quote
select table1.customer_id,type,start_date,end_date,Program_id from table1 join table2 on table1.customer_id = table2.customer_id
where type not in ('aa','cc','g2') and code=3 and (start_date is null or end_date is null or program_id is null)
Go to Top of Page

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 02/06/2014 :  10:15:02  Show Profile  Reply with Quote
The code = 3 only applies to type = 'g2'.

Thus how would you change the sql?
Go to Top of Page

ScottPletcher
Aged Yak Warrior

USA
550 Posts

Posted - 02/06/2014 :  12:23:28  Show Profile  Reply with Quote


select table1.customer_id, table1.type, table2.start_date, table2.end_date, table2.Program_id 
from table1 
join table2 on 
    table1.customer_id = table2.customer_id
where
    (table1.type not in ( 'aa', 'cc' ) and (table1.type <> 'g2' or table1.code = 3)) and
    (table2.start_date is null or table2.end_date is null or table2.program_id is null)


Edited by - ScottPletcher on 02/06/2014 12:25:44
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000