Selecting data from a table - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting data from a table

Author

Topic

prajeeshkk
Starting Member

3 Posts

Posted - 2009-12-24 : 02:10:33

Hi,
suppose below is my table.

User_id Depth childcount
3 0 1
3 1 10
4 0 1
5 0 1
5 1 26
5 2 61
5 3 24
5 4 13
5 5 11
5 6 7
5 7 3
5 8 1
6 0 1
7 0 1

I want to select user_ids who is having minimum 5 childs in depths 1 to 5. here the user_id 5 will come .

Please help me to write down a query to achieve this.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-24 : 02:15:09

[code]
SELECT user_id FROM
(
select 3 as User_id, 0 Depth, 1 childcount union all select
3, 1, 10 union all select
4, 0, 1 union all select
5, 0, 1 union all select
5, 1, 26 union all select
5, 2, 61 union all select
5, 3, 24 union all select
5, 4, 13 union all select
5, 5, 11 union all select
5, 6, 7 union all select
5, 7, 3 union all select
5, 8, 1 union all select
6, 0, 1 union all select
7, 0, 1
)s WHERE depth BETWEEN 1 and 5
GROUP BY user_id
HAVING count(user_id) >= 5
[/code]

Sachin.Nand

2937 Posts

Posted - 2009-12-24 : 02:46:29

I think there is no need for the where clause.

PBUH

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-24 : 03:01:10

quote:
Originally posted by Idera

I think there is no need for the where clause.

PBUH

for given data no need of where clause but in the question he mention the depth between 1 to 5 so that i kept the where clause......

Sachin.Nand

2937 Posts

Posted - 2009-12-24 : 04:20:25

Yes thats right.Missed on that one.

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-12-26 : 07:22:23

Your question is not completely in spec. I guess you're looking for Relational Division.

SET  NOCOUNT ON
DECLARE @your_table TABLE
(User_id int,
 Depth int,
 childcount int)
 
INSERT INTO @your_table
select 3 as User_id, 0 Depth, 1 childcount union all select
3, 1, 10 union all select
4, 0, 1  union all select
5, 0, 1  union all select
5, 1, 26 union all select
5, 2, 61 union all select
5, 3, 24 union all select
5, 4, 13 union all select
5, 5, 11 union all select
5, 6, 7  union all select
5, 7, 3  union all select
5, 8, 1  union all select
6, 0, 1  union all select
7, 0, 1
 
SELECT user_id 
  FROM @your_table
 GROUP BY user_id
HAVING SUM(CASE WHEN depth IN (1,2,3,4,5) THEN 1 ELSE 0 END)>=5

--OR
SELECT user_id
  FROM @your_table
 GROUP BY user_id
HAVING MAX(CASE WHEN depth = 1 THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN depth = 2 THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN depth = 3 THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN depth = 4 THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN depth = 5 THEN 1 ELSE 0 END) = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources