help in sql

• leodec

  SSC Enthusiast

  Points: 132

  More actions

  October 1, 2010 at 2:20 pm

  #387923

  Hi guys

  i need little help in below mentioned sql.... need to find out any of col1,col2,col3 is null and in last if col3 is null then in that case col2 should have A1,A2,A3 values only not other than this.....

  select col1, col2, col3, id from tab1, tab2 where tab1.id=tab2.id

  and ((col1 is not null or (col2 is null or col3 is null))

  or (col2 is not null or (col1is null or col3 is null))

  or (col3 is not null or (col1 is null or col2 is null))

  and (col3 is null and col2 in ('A1','A2','A3')))

  If you need more info please let me know

  thanks & regards

• getoffmyfoot

  Hall of Fame

  Points: 3338

  More actions

  October 1, 2010 at 2:32 pm

  #1230517

  payal.beri (10/1/2010)

  ---

  Hi guys

  i need little help in below mentioned sql.... need to find out any of col1,col2,col3 is null and in last if col3 is null then in that case col2 should have A1,A2,A3 values only not other than this.....

  select col1, col2, col3, id from tab1, tab2 where tab1.id=tab2.id

  and ((col1 is not null or (col2 is null or col3 is null))

  or (col2 is not null or (col1is null or col3 is null))

  or (col3 is not null or (col1 is null or col2 is null))

  and (col3 is null and col2 in ('A1','A2','A3')))

  If you need more info please let me know

  thanks & regards

  Hmm, is this what you are after?

  SELECT col1, col2, col3, id from tab1, tab2

  WHERE tab1.id = tab2.id

  AND COALESCE(col1, col2, col3, '' ) != ''

  AND col2 IN ('A1', 'A2', 'A3')

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  October 1, 2010 at 2:37 pm

  #1230519

  Can you provide sample data of what you are trying to achieve?

  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

• leodec

  SSC Enthusiast

  Points: 132

  More actions

  October 2, 2010 at 6:12 am

  #1230688

  hi

  thanks for reply data is some how like this

  col1 col2 col3

  ab bc null

  null A1 null

  null null null

  cd bc1 null

  null A2 01/01/09

  ab A3 null

  i need to retrieve the rows with null in any col1, col2, col3 but don;t need the combination where col3 is null and col2 has values of bc & bc1

  regards

• getoffmyfoot

  Hall of Fame

  Points: 3338

  More actions

  October 2, 2010 at 7:08 am

  #1230690

  leodec (10/2/2010)

  ---

  hi

  thanks for reply data is some how like this

  col1 col2 col3

  ab bc null

  null A1 null

  null null null

  cd bc1 null

  null A2 01/01/09

  ab A3 null

  i need to retrieve the rows with null in any col1, col2, col3 but don;t need the combination where col3 is null and col2 has values of bc & bc1

  regards

  How about this -

  select col1, col2, col3

  FROM tab

  WHERE (col1 IS NULL OR col2 IS NULL OR col3 IS NULL)

  AND NOT (col3 IS NULL AND col2 IN ('bc', 'bc1'))

  Does that work for you?