SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

308 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

308 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
Constraint Violating Yak Guru

USA
425 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  
 New 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