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: Today @ 8:45 AM
Points: 5,100, Visits: 11,899
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.
Post #1480672
Posted Saturday, August 3, 2013 2:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 5,100, Visits: 11,899
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.
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: Today @ 8:45 AM
Points: 5,100, Visits: 11,899
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.
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: Today @ 8:45 AM
Points: 5,100, Visits: 11,899
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.
Post #1480684
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse