SQL Server Performance

displaying records only if both the values of a field are avaliable

Discussion in 'ALL SQL SERVER QUESTIONS' started by Surekha, Aug 3, 2013.

  1. Surekha New Member

    i have a table with 5 fields
    field1 ie STATUS will have values as 'd' and 'i'
    if the field1 of table has both the values then only the data shld e displayed
    but if the field1 has only value 'd' then no data shld be displayed
    i have tried select * from #temp1 where col1='d' and col1 ='i'
    but nothing gets displayed

    i have given here for table1
    but there are 5 tables total
    and i need the result output with union all

    example
    select * from #5_TEMP
    union all
    select * from #4_TEMP
    union all
    select * from #3_TEMP

    if all this 3 tables has STATUS 'd' and 'a' then the union will be of all 3 tables

    if 4_TEMP has data of only STATUS='d' then table 4_TEMP will not be included in the union
    the the union will be as

    select * from #5_TEMP
    union all
    select * from #3_TEMP

    Attached Files:

  2. davidfarr Member

    You don't necessarily need to exclude any tables from the UNION syntax.
    Instead; just apply a WHERE clause on each table that will return no rows if the table condition is not met.
    Something like this;

    Code:
    select * from #5_TEMP where 0 < (select count([STATUS]) from #5_TEMP where [STATUS]<>'d')
    union all
    select * from #4_TEMP where 0 < (select count([STATUS]) from #4_TEMP where [STATUS]<>'d')
    union all
    select * from #3_TEMP where 0 < (select count([STATUS]) from #3_TEMP where [STATUS]<>'d')
    union all
    select * from #2_TEMP where 0 < (select count([STATUS]) from #2_TEMP where [STATUS]<>'d')
    union all
    select * from #1_TEMP where 0 < (select count([STATUS]) from #1_TEMP where [STATUS]<>'d')
    

Share This Page