Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

displaying records only if both the values of a field are avaliable Expand / Collapse
Author
Message
Posted Saturday, August 3, 2013 1:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
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
Post #1480669
Posted Saturday, August 3, 2013 2:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
ssurekha2000 (8/3/2013)
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


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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1480672
Posted Saturday, August 3, 2013 2:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1480673
Posted Saturday, August 3, 2013 2:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
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


Status ReNo ItmDesc Qty
d * X
d 2.0 XX
i 2.2
i 2.3
i 2.4
i 2.5
i 2.6
i 2.7
i 2.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
Post #1480675
Posted Saturday, August 3, 2013 2:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1480677
Posted Saturday, August 3, 2013 3:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
sample data attached
i hope this is the proper for you to proceed


  Post Attachments 
Book1.xlsx (3 views, 9.50 KB)
Post #1480681
Posted Saturday, August 3, 2013 4:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
ssurekha2000 (8/3/2013)
sample data attached
i 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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1480684
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse