Single Query with one sql statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Single Query with one sql statement

Author

Topic

afzaal820
Starting Member

19 Posts

Posted - 2011-08-15 : 03:28:43

OID DataElementID Value
151 3020004 1305106
153 3020004 1305104
153 3020004 1305106
207 3020004 1305106
201 3020004 1305104

Select OID where the DataElementID is 3020004 and value is 1305104 and 1305106

In that case the result is 153.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-15 : 03:34:37

[code]
select OID
from data
where DataElementID = 3020004
and Value in (1305104, 1305106)
group by OID, DataElementID
having count(*) = 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

afzaal820
Starting Member

19 Posts

Posted - 2011-08-15 : 04:01:39

Hi khtan

Thanks for help but above data is just a sample, I dont know in advance that count is two.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-15 : 04:09:43

then how will your supply the Value to the query ?

KH
[spoiler]Time is always against us[/spoiler]

afzaal820
Starting Member

19 Posts

Posted - 2011-08-15 : 04:22:33

What I can input is DataElementID and Values to lookup.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-15 : 05:34:43

[code]
declare @val table
(
Value int
)

insert into @val select 1305104
insert into @val select 1305106

select OID
from yourtable d
where DataElementID = 3020004
and exists
(
select *
from @val x
where x.Value = d.Value
)
group by OID, DataElementID
having count(*) = (select count(*) from @val)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources