August 3, 2013 at 1:26 am
i have a table with 5 fields
field1 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
August 3, 2013 at 2:03 am
ssurekha2000 (8/3/2013)
i have a table with 5 fieldsfield1 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
Usually I would ask for DDL, sample data etc. - as per the link in my signature. But it sounds like you want something like this:
If exists (select 1 from table where col1 = 'd') and exists (select 1 from table where col1 = 'i')
begin
select * from table
end
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 3, 2013 at 2:05 am
Nothing is displayed the way you have tried because you are saying:
'Give me all rows where col1 is d and col1 is i'
Col1 cannot simultaneously contain both values - hence no data is returned.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 3, 2013 at 2:26 am
Thanks for the valuable info
But i need this way
but this gives error
whts the correct way?
If exists (select 1 from #1_TEMP where Status= 'd') and exists (select 1 from #1_TEMP where Status= 'i')
begin
select * from #1_TEMP
end
UNION ALL
If exists (select 1 from #2_TEMP where Status= 'd') and exists (select 1 from #2_TEMP where Status= 'i')
begin
select * from #2_TEMP
end
UNION ALL
If exists (select 1 from #3_TEMP where Status= 'd') and exists (select 1 from #3_TEMP where Status= 'i')
begin
select * from #3_TEMP
end
UNION ALL
If exists (select 1 from #4_TEMP where Status= 'd') and exists (select 1 from #4_TEMP where Status= 'i')
begin
select * from #4_TEMP
end
UNION ALL
If exists (select 1 from #5_TEMP where Status= 'd') and exists (select 1 from #5_TEMP where Status= 'i')
begin
select * from #5_TEMP
end
StatusReNo ItmDescQty
d* X
d2.0 XX
i2.2
i2.3
i2.4
i2.5
i2.6
i2.7
i2.9
some table can conatin only data with status ='d'
the final result shld contain the tables with data 'd' and 'i'
if it conatins only 'd' then the table shld not be unioned
August 3, 2013 at 2:53 am
Your first post mentions one table. Now you are mentioning many - how was I supposed to know this?
I made a mistake in trying to help you, because you did not provide all of the information which was relevant to your problem.
Now I will revert to a request for DDL, sample data and desired results in a format which can be cut & pasted into SSMS.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 3, 2013 at 3:04 am
sample data attached
i hope this is the proper for you to proceed
August 3, 2013 at 4:42 am
ssurekha2000 (8/3/2013)
sample data attachedi hope this is the proper for you to proceed
select from table1
where exists (select 1 from table1 where col1 = 'd') and exists (select 1 from table1 where col1 = 'i')
union all
select from table2
where exists (select 1 from table2 where col1 = 'd') and exists (select 1 from table2 where col1 = 'i')
etc
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply