Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jassie
Constraint Violating Yak Guru
332 Posts |
 Posted - 2014-02-05 : 16:31:37
|
| 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_id1. 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. and2. 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? |
|
|
sree203509
Starting Member
2 Posts |
Posted - 2014-02-06 : 00:24:05
|
| select table1.customer_id,type,start_date,end_date,Program_id from table1 join table2 on table1.customer_id = table2.customer_idwhere type not in ('aa','cc','g2') and code=3 and (start_date is null or end_date is null or program _id is null) |
 |
|
|
jassie
Constraint Violating Yak Guru
332 Posts |
Posted - 2014-02-06 : 10:15:02
|
| The code = 3 only applies to type = 'g2'.Thus how would you change the sql? |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-02-06 : 12:23:28
|
| [code]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_idwhere (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)[/code] |
|
|
|
|
|
|
|
|
t-sql where clause