select help - SQL Server Forums

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

select help

Author

Topic

galove
Starting Member

1 Post

Posted - 2010-02-14 : 04:23:06

hello. I think this select query is easy but i couldnt do it.

i have a table and data like that;

id valueid values
5 59 125
5 60 30
5 61 10
6 59 125
6 60 20
6 61 10

i want to select id which have value (125 and 30 and 10).

if i do this with "and" , it selects no data because there are no columns.

if i do this with "or" it selects 5,6.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-14 : 05:38:15

here is one way:

-- making testdata
declare @test table (id int, valueid int, [values] int)
insert @test
select 5, 59, 125 union all
select 5, 60, 30 union all
select 5, 61, 10 union all
select 6, 59, 125 union all
select 6, 60, 20 union all
select 6, 61, 10

-- show testdata
select * from @test

-- solution
select distinct id from @test t1
where exists (select * from @test t2 where t1.id=t2.id and t2.[values] = 125 )
and  exists (select * from @test t2 where t1.id=t2.id and t2.[values] = 30 )
and  exists (select * from @test t2 where t1.id=t2.id and t2.[values] = 10 )

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 11:09:08

quote:
Originally posted by galove

hello. I think this select query is easy but i couldnt do it.

i have a table and data like that;

id valueid values
5 59 125
5 60 30
5 61 10
6 59 125
6 60 20
6 61 10

i want to select id which have value (125 and 30 and 10).

if i do this with "and" , it selects no data because there are no columns.

if i do this with "or" it selects 5,6.


select id
from table
where values in (125,30,10)
group by id
having count(distinct id) =3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-02-14 : 12:32:36

select * from
(
select id, count(distinct [values]) as valuecount
from yourtable
where [values] in (125,30,10)
group by id
)t where valuecount=3

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-14 : 16:24:19

quote:
Originally posted by visakh16

quote:
Originally posted by galove

hello. I think this select query is easy but i couldnt do it.

i have a table and data like that;

id valueid values
5 59 125
5 60 30
5 61 10
6 59 125
6 60 20
6 61 10

i want to select id which have value (125 and 30 and 10).

if i do this with "and" , it selects no data because there are no columns.

if i do this with "or" it selects 5,6.

select id
from table
where values in (125,30,10)
group by id
having count(distinct id [values]) =3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SELECT DISTINCT id
  FROM @test t1
 WHERE NOT EXISTS 
       (SELECT 125 UNION ALL 
        SELECT 30 UNION ALL 
        SELECT 10
        EXCEPT
        SELECT [Values]
          FROM @test t2
         WHERE t1.id = t2.id);

Or for a lot of solution see :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129157

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-14 : 16:34:20

Your query is same as visakh16 posted!
see my suggestion in the link above post.

quote:
Originally posted by Idera

select * from
(
select id, count(distinct [values]) as valuecount
from yourtable
where [values] in (125,30,10)
group by id
)t where valuecount=3

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources