locked
plz help me in this sql statement RRS feed

  • Question

  • hi

    i have one table which included 4 fields


    field1      field2         field3          field4

    a              b            null             null

    a              c            null             null

    null          null            a               b

     

    what is the statement which give me the value which in field1 and field1 but not in field3 and  field4


    result like this


    field         field2

     a               c


    plz help me in this statement

    Friday, October 10, 2008 11:46 AM

Answers

  • Try this:

    Code Snippet

    declare @Temp table(field1 varchar(50), field2 varchar(50),field3 varchar(50),field4 varchar(50))

    insert into @Temp

    select 'a','b',null, null

    union all select 'a','c', null, null

    union all select null,null,'a','b'

    select a.field1,a.field2 from (select * from @Temp where not field1 is null and not field2 is null ) a FULL OUTER JOIN (select * from @Temp where not field3 is null and not field4 is null ) b on a.field1=b.field3 and a.field2=b.field4 where b.field3 is null and b.field4 is null

     

     

     

    Friday, October 10, 2008 12:01 PM
  • This would also work for you....

     

    Code Snippet

    CREATE TABLE #T

    (

    field1 varchar(50)

    , field2 varchar(50)

    ,field3 varchar(50)

    ,field4 varchar(50)

    )

    GO

     

    insert into #T values('a','b',null, null)

    insert into #T values('a','c', null, null)

    insert into #T values( null,null,'a','b')

     

    select a.field1,a.field2

    from #T a

    where not exists

    (

    select *

    from #T

    where a.field1 = #T.field3

    and a.field2 = #T.field4

    )

    and a.field1 is not null

    and a.field2 is not null

    go

    drop table #T

     

     

    HTH

     

    Jay

     

    Friday, October 10, 2008 12:26 PM
  • The above, will exclude rows if either Field1 or Field2 is null, if you want to return values if 1 of the values is populated, change the WHERE to this:

    Code Snippet

     

    CREATE TABLE #T

    (

    field1 varchar(50)

    , field2 varchar(50)

    ,field3 varchar(50)

    ,field4 varchar(50)

    )

    GO

     

    insert into #T values('a','b',null, null)

    insert into #T values('a','c', null, null)

    insert into #T values(NULL,'c', null, null)

    insert into #T values( null,null,'a','b')

     

    select a.field1,a.field2

    from #T a

    where not exists

    (

    select *

    from #T

    where a.field1 = #T.field3

    and a.field2 = #T.field4

    )

    and (a.field1 is not null

    or a.field2 is not null)

    go

    drop table #T

     

     

     

    Friday, October 10, 2008 12:29 PM

All replies

  • Try this:

    Code Snippet

    declare @Temp table(field1 varchar(50), field2 varchar(50),field3 varchar(50),field4 varchar(50))

    insert into @Temp

    select 'a','b',null, null

    union all select 'a','c', null, null

    union all select null,null,'a','b'

    select a.field1,a.field2 from (select * from @Temp where not field1 is null and not field2 is null ) a FULL OUTER JOIN (select * from @Temp where not field3 is null and not field4 is null ) b on a.field1=b.field3 and a.field2=b.field4 where b.field3 is null and b.field4 is null

     

     

     

    Friday, October 10, 2008 12:01 PM
  • This would also work for you....

     

    Code Snippet

    CREATE TABLE #T

    (

    field1 varchar(50)

    , field2 varchar(50)

    ,field3 varchar(50)

    ,field4 varchar(50)

    )

    GO

     

    insert into #T values('a','b',null, null)

    insert into #T values('a','c', null, null)

    insert into #T values( null,null,'a','b')

     

    select a.field1,a.field2

    from #T a

    where not exists

    (

    select *

    from #T

    where a.field1 = #T.field3

    and a.field2 = #T.field4

    )

    and a.field1 is not null

    and a.field2 is not null

    go

    drop table #T

     

     

    HTH

     

    Jay

     

    Friday, October 10, 2008 12:26 PM
  • The above, will exclude rows if either Field1 or Field2 is null, if you want to return values if 1 of the values is populated, change the WHERE to this:

    Code Snippet

     

    CREATE TABLE #T

    (

    field1 varchar(50)

    , field2 varchar(50)

    ,field3 varchar(50)

    ,field4 varchar(50)

    )

    GO

     

    insert into #T values('a','b',null, null)

    insert into #T values('a','c', null, null)

    insert into #T values(NULL,'c', null, null)

    insert into #T values( null,null,'a','b')

     

    select a.field1,a.field2

    from #T a

    where not exists

    (

    select *

    from #T

    where a.field1 = #T.field3

    and a.field2 = #T.field4

    )

    and (a.field1 is not null

    or a.field2 is not null)

    go

    drop table #T

     

     

     

    Friday, October 10, 2008 12:29 PM
  •  dear mobin.p   and  Jay Bonk

    i'm very happy

    thank you very very very much

    sorry my english  is not good but

    thank you
    thank you
    thank you

     

    Friday, October 10, 2008 12:46 PM