Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ssurekha2000
ssurekha2000
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8375 Visits: 19500
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8375 Visits: 19500
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ssurekha2000
ssurekha2000
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8375 Visits: 19500
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ssurekha2000
ssurekha2000
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 207
sample data attached
i hope this is the proper for you to proceed
Attachments
Book1.xlsx (3 views, 9.00 KB)
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8375 Visits: 19500
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search