get records within same group and with condition

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 2008 Forums

Transact-SQL (2008)

get records within same group and with condition

Author

Topic

jutiyi
Starting Member

9 Posts

Posted - 2012-01-10 : 21:03:56

I have a table with following record.

ParentID Value
1 NULL
1 NULL
2 3
2 NULL
3 4
3 10

How to write a query to get records with same parentID and at least one value is NULL and NON Null..

For example, returned record should be
ParentID Value
2 3
2 NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-10 : 21:38:28

[code]
select t.*
from atable t
inner join
(
select ParentID
from atable
group by ParentID
having count(Value) <> count(*)
and min(Value) is not null
) p on t.ParentID = p.ParentID
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 01:24:41

[code]
SELECT ParentID,Value
FROM
(
SELECT COUNT(CASE WHEN Value IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY ParentID) AS NCnt,
COUNT(CASE WHEN Value IS NOT NULL THEN 1 ELSE NULL END) OVER (PARTITION BY ParentID) AS NNCnt,*
FROM Table
)t
WHERE NCnt>0
AND NNCnt > 0
[/code]

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

jutiyi
Starting Member

9 Posts

Posted - 2012-01-11 : 01:27:46

quote:

Originally posted by khtan


select	t.*
from	atable t
	inner join
	(
		select	ParentID
		from	atable
		group by ParentID
		having	count(Value)	<> count(*)
		and	min(Value)	is not null
	) p	on	t.ParentID	= p.ParentID

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

Thanks,,It works. But here I come to think about some unique parameter involved.

Table 1
ParentID ChildID
1 1
1 2

Table 2
ID Location Cost
1 1 100
1 2 200

Table 3
Location Name
1 Europe
2 Asia

I want to get the final output with outer join table
ParentID ChildID Location Name Cost
1 1 1 Europe 100
1 2 1 Europe NULL
1 1 2 Asia 200
1 2 2 Asia NULL

i use other join, i can only get where Location and Name are NULL also..but I want it to follow the parent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 02:16:24

isnt it matter of joining other tables to your current query to get related info?

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

jutiyi
Starting Member

9 Posts

Posted - 2012-01-11 : 02:37:55

quote:
Originally posted by visakh16

isnt it matter of joining other tables to your current query to get related info?

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

I use other join, only get

ParentID ChildID Location Name Cost
1 1 1 Europe 100
1 2 NULL NULL NULL
1 1 2 Asia 200
1 2 NULL NULL NULL
which is not I want...I not sure why to make the same group parentID follow the Location and Name of the group which has value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 03:38:27

can you show the current query please?

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

jutiyi
Starting Member

9 Posts

Posted - 2012-01-11 : 21:50:40

quote:
Originally posted by visakh16

can you show the current query please?

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

Table 1
ParentID ChildID
1 1
1 2
3 3
3 5
6 7

Table2
ID Location Cost
1 1 50.0000
1 2 400.0000
2 1 50.0000
3 2 100.0000

Table3
Location Name
1 Asia
2 Europe

Table4
ID Name
1 P_1
2 P_2
3 P_3
4 P_4
5 P_5
6 P_6
7 P_7

SELECT Table1.ParentID, Table4.ID, Table2.Cost, Table3.Location
FROM Table3 INNER JOIN
Table2 ON Table3.Location = Table2.Location RIGHT OUTER JOIN
Table1 INNER JOIN
Table4 ON Table1.ChildID = Table4.ID ON Table2.ID = Table4.ID
GROUP BY Table1.ParentID, Table4.ID, Table2.Cost, Table3.Location

The output I get is
ParentID ChildID location Cost
1 1 1 50.0000
1 1 2 400.0000
1 2 1 50.0000
3 3 2 100.0000
3 5 NULL NULL
6 7 NULL NULL

But What I want is
ParentID ChildID location Cost
1 1 2 400.0000
1 2 2 NULL
3 3 2 100.0000
3 5 2 NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-11 : 23:51:20

[code]
;with cte as
(
select t1.ParentID, t1.ChildID, t3.Location, t2.Cost
from Table1 t1
cross join Table3 t3
left join Table2 t2 on t1.ChildID = t2.ID
and t3.Location = t2.Location
)
select c.*
from cte c
inner join
(
select ParentID, Location
from cte
group by ParentID, Location
having count(Cost) <> count(*)
and min(Cost) is not null
) p on c.ParentID = p.ParentID
and c.Location = p.Location
order by c.ParentID, c.ChildID, c.Location, c.Cost
[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-01-12 : 03:24:38

Try this. I have not tested this

select parentid from table
group by parentid
having min(case when value is null then -1000 else value end) =-1000 and max(value) is not null

Madhivanan

Failing to plan is Planning to fail

jutiyi
Starting Member

9 Posts

Posted - 2012-01-12 : 20:35:31

quote:

Originally posted by khtan


;with cte as
(
	select	t1.ParentID, t1.ChildID, t3.Location, t2.Cost
	from	Table1 t1
		cross join Table3 t3
		left join Table2 t2	on	t1.ChildID	= t2.ID
					and	t3.Location	= t2.Location
)
select	c.*
from	cte c
	inner join
	(
		select	ParentID, Location
		from	cte
		group by ParentID, Location
		having	count(Cost)	<> count(*)
		and	min(Cost)	is not null
	) p	on	c.ParentID	= p.ParentID
		and	c.Location	= p.Location
order by c.ParentID, c.ChildID, c.Location, c.Cost

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

Thanks.. The query works great. I have one more question. If my table 2 consists of one more column called priority.

Table 2
ID Location Priority Cost
1 1 1 50.0000
1 2 1 400.0000
2 1 2 50.0000
3 2 2 100.0000

So output I get should be
ParentID ChildID location Priority Cost
1 1 2 1 400.0000
1 2 2 1 NULL
3 3 2 2 100.0000
3 5 2 2 NULL

Not sure if my thinking was correct. Is it I need to get the distinct priority from table 2 by group by as another derived table then outer join the table 2?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-12 : 23:22:23

what do you want to do with that priority column ?

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

jutiyi
Starting Member

9 Posts

Posted - 2012-01-13 : 00:06:11

quote:
Originally posted by khtan

what do you want to do with that priority column ?

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

sama as location, it is consider part of the grouping,
ParentID, Location and Priority is the unique key of the group

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-13 : 02:08:21

[code]
;with cte as
(
select t1.ParentID, t1.ChildID, t3.Location, t2.Cost, t2.priority
from Table1 t1
cross join Table3 t3
left join Table2 t2 on t1.ChildID = t2.ID
and t3.Location = t2.Location
)
select c.*
from cte c
inner join
(
select ParentID, Location, priority
from cte
group by ParentID, Location, priority
having count(Cost) <> count(*)
and min(Cost) is not null
) p on c.ParentID = p.ParentID
and c.Location = p.Location
and c.priority = p.priority
order by c.ParentID, c.ChildID, c.Location, c.Cost
[/code]

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

jutiyi
Starting Member

9 Posts

Posted - 2012-01-13 : 02:35:07

quote:

Originally posted by khtan


;with cte as
(
	select	t1.ParentID, t1.ChildID, t3.Location, t2.Cost, t2.priority
	from	Table1 t1
		cross join Table3 t3
		left join Table2 t2	on	t1.ChildID	= t2.ID
					and	t3.Location	= t2.Location
)
select	c.*
from	cte c
	inner join
	(
		select	ParentID, Location, priority
		from	cte
		group by ParentID, Location, priority
		having	count(Cost)	<> count(*)
		and	min(Cost)	is not null
	) p	on	c.ParentID	= p.ParentID
		and	c.Location	= p.Location
                and     c.priority      = p.priority
order by c.ParentID, c.ChildID, c.Location, c.Cost

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

;with cte as
(
select t1.ParentID, t1.ChildID, t3.Location, t2.Cost, t2.priority
from Table1 t1
cross join Table3 t3
left join Table2 t2 on t1.ChildID = t2.ID
and t3.Location = t2.Location
)

THE result I get is
1 1 1 50.0000 1
1 2 1 50.0000 2
1 1 2 400.0000 1
1 2 2 NULL NULL
3 3 1 NULL NULL
3 5 1 NULL NULL
3 3 2 100.0000 2
3 5 2 NULL NULL
6 7 1 NULL NULL
6 7 2 NULL NULL

But I should get
1 1 1 50.0000 1
1 2 1 50.0000 2
1 1 2 400.0000 1
1 2 2 NULL 1
3 3 1 NULL NULL
3 5 1 NULL NULL
3 3 2 100.0000 2
3 5 2 NULL 2
6 7 1 NULL NULL
6 7 2 NULL NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-13 : 03:18:17

can you post your sample data for Table1,2,3 in form of INSERT INTO . . .

It makes it much easier for us to help you.

Also when posting your expected result, do include the header clearly and most importantly enclosed the expected result in [code] tag

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources