Help with returning list of groups.

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)

Help with returning list of groups.

Author

Topic

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 04:47:46

I have a report query that uses a CURSOR to store a list of group names to be reported on.
The issue is that one group 'rejected members' is nearly always blank, but not always, but they want to show any other empty groups, so a simple INNER JOIN won't do the trick.
The select for my cursor is:
[CODE]
SELECT DISTINCT
DG.ID,
DG.Name,
DG.Number
FROM
LiabilityRunDatasetGroup DG
WHERE
DG.GroupDefinitionID = @GroupDefinitionID
ORDER BY
DG.Number
[/CODE]
By adding in:


INNER JOIN 
	LiabilityRunMemberDatasetGroup MDG ON MDG.DatasetGroupID = DG.ID

I can return a list of groups that have members in, but, as stated above, I only want to exclude groups with the field 'Number' as 0 and where there are no members.
The contents of LiabilityRunDatasetGroup are sub-group names within group IDs, i.e.
GroupID___Name___________Number
1__________Rejected Group_____0
1__________Group 1____________1
1__________Group 2____________2
1__________Group 3____________3
2__________Rejected Group_____0
2__________Group 1____________1
2__________Group 2____________2
2__________Group 3____________3
2__________Group 4____________4
...
The table LiabilityRunMemberDatasetGroup contains only two columns:
MemberID and DatasetGroupID, i.e.
MemberID___DatasetGroupID
Member1_____1
Member2_____1
Member3_____1
Member4_____1
Member5_____1
Member6_____1
Member1_____2
Member2_____2
Member3_____2
Member4_____2
...
I hope this makes sense - I just can't figure out the syntax for the query.
Any help gratefully received.
Martin

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 05:16:37

Can you post some sample data o/p?

PBUH

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 06:05:00

Hi
Yes, of course.
LiabilityRunDatasetGroup has five columns:
ID, GroupDefinitionID, Name, Number, Gender.
LiabilityRunMemberDatasetGroup contains two columns:
MemberID, DatasetGroupID

LiabilityRunDatasetGroup.ID maps to LiabilityRunMemberDatasetGroup.DatasetGroupID

Data from LiabilityRunDatasetGroup:
15842 1336 ** Rejected Members' Group ** 0 U
15843 1336 Test Group 1 1 U
15844 1336 Test Group 2 2 U
15845 1336 Test Group 3 3 U
15846 1336 Test Group 4 4 U
15847 1336 Test Group 5 5 U
15848 1336 Test Group 6 6 U
15849 1337 ** Rejected Members' Group ** 0 U
15850 1337 Test Group 1 1 U
15851 1337 Test Group 2 2 U
15852 1337 Test Group 3 3 U
15853 1337 Test Group 4 4 U
15854 1337 Test Group 5 5 U
15855 1337 Test Group 6 6 U
15856 1338 ** Rejected Members' Group ** 0 U
15857 1338 Test Group 1 1 U
15858 1338 Test Group 2 2 U
...
Data in :
Member01 15842
Member02 15842
Member03 15843
Member04 15843
Member05 15843
Member06 15844
Member07 15844
Member08 15844
Member09 15845
Member10 15845
Member11 15845
Member12 15845
Member13 15846
Member14 15846
Member15 15847
Member16 15848
Member17 15848
Member18 15848
Member19 15848
Member20 15850
Member21 15850
Member22 15850
Member23 15850
Member24 15852
Member25 15852
Member26 15852
Member27 15853
Member28 15853
Member29 15854
Member30 15855
Member31 15855
Member32 15855
Member33 15855
Member34 15857

So for this example, one rejected group has members, the rest don't, and there is also one none-rejected member group that has no members, but I still want that one to display.
HTH
Martin

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 06:19:18

FYI
I would pass in GroupDefinitionID as my argument, so for the data above, if passing in 1336, I'd expect:

** Rejected Members' Group **
Test Group 1
Test Group 2
Test Group 3
Test Group 4
Test Group 5
Test Group 6

whereas for 1337,

Test Group 1
Test Group 2
Test Group 3
Test Group 4
Test Group 5
Test Group 6

HTH
Martin

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 06:37:05

[code]
SELECT DISTINCT
DG.ID,
DG.Name,
DG.Number
FROM
LiabilityRunDatasetGroup DG
INNER JOIN
LiabilityRunMemberDatasetGroup MDG ON MDG.DatasetGroupID = DG.ID

WHERE
DG.GroupDefinitionID = @GroupDefinitionID and LiabilityRunDatasetGroup<>0
ORDER BY
DG.Number
[/code]

PBUH

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 06:51:28

quote:

Originally posted by Sachin.Nand


SELECT DISTINCT
	DG.ID,
	DG.Name,
	DG.Number
FROM
	LiabilityRunDatasetGroup DG
	INNER JOIN 
	LiabilityRunMemberDatasetGroup MDG ON MDG.DatasetGroupID = DG.ID

WHERE
	DG.GroupDefinitionID = @GroupDefinitionID and LiabilityRunDatasetGroup<>0
ORDER BY
	DG.Number

PBUH

Thanks, but don't know what 'LiabilityRunDatasetGroup<>0' is doing as that's a table?
Martin

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 06:54:28

Sorry it should be Number<>0

PBUH

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 07:06:35

Thanks, but surely the inner join already excludes all empty groups (irrespective of the value of Number)?
I want only empty rejected groups excluded, but empty non-rejected groups should still be listed.
Or am I missing something?
Thanks
Martin

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 07:14:05

I guess I am missing something.Just a wild guess.What happens if you replace inner join with a left join?

PBUH

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 07:16:13

LEFT JOIN returns all the groups bar the rejected group, whereas it should show for the first ID.
For testing 9as it is not real data) I set up the following:


DECLARE @LiabilityRunDatasetGroup TABLE
(
	[ID] int, 
	[GroupDefinitionID] int, 
	[Name] varchar(255), 
	[Number] int, 
	[Gender] varchar(1)
)

INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15842, 1336, '** Rejected Members'' Group **', 0, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15843, 1336, 'Test Group 1', 1, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15844, 1336, 'Test Group 2', 2, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15845, 1336, 'Test Group 3', 3, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15846, 1336, 'Test Group 4', 4, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15847, 1336, 'Test Group 5', 5, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15848, 1336, 'Test Group 6', 6, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15849, 1337, '** Rejected Members'' Group **', 0, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15850, 1337, 'Test Group 1', 1, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15851, 1337, 'Test Group 2', 2, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15852, 1337, 'Test Group 3', 3, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15853, 1337, 'Test Group 4', 4, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15854, 1337, 'Test Group 5', 5, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15855, 1337, 'Test Group 6', 6, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15856, 1338, '** Rejected Members'' Group **', 0, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15857, 1338, 'Test Group 1', 1, 'U')
INSERT INTO @LiabilityRunDatasetGroup
	VALUES (15858, 1338, 'Test Group 2', 2, 'U')

DECLARE @LiabilityRunMemberDatasetGroup TABLE
(
	MemberID varchar(10), 
	DatasetGroupID int
)

INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member01', 15842)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member02', 15842)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member03', 15843)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member04', 15843)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member05', 15843)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member06', 15844)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member07', 15844)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member08', 15844)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member09', 15845)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member10', 15845)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member11', 15845)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member12', 15845)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member13', 15846)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member14', 15846)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member15', 15847)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member16', 15848)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member17', 15848)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member18', 15848)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member19', 15848)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member20', 15850)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member21', 15850)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member22', 15850)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member23', 15850)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member24', 15852)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member25', 15852)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member26', 15852)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member27', 15853)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member28', 15853)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member29', 15854)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member30', 15855)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member31', 15855)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member32', 15855)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member33', 15855)
INSERT INTO @LiabilityRunMemberDatasetGroup
	VALUES('Member34', 15857)

SELECT DISTINCT
	DG.ID,
	DG.Name,
	DG.Number
FROM
	@LiabilityRunDatasetGroup DG
	INNER JOIN
	@LiabilityRunMemberDatasetGroup MDG ON MDG.DatasetGroupID = DG.ID

WHERE
	DG.GroupDefinitionID = 1336 AND DG.Number <> 0
ORDER BY
	DG.Number

SELECT DISTINCT
	DG.ID,
	DG.Name,
	DG.Number
FROM
	@LiabilityRunDatasetGroup DG
	LEFT JOIN
	@LiabilityRunMemberDatasetGroup MDG ON MDG.DatasetGroupID = DG.ID

WHERE
	DG.GroupDefinitionID = 1337 AND DG.Number <> 0
ORDER BY
	DG.Number

SELECT DISTINCT
	DG.ID,
	DG.Name,
	DG.Number
FROM
	@LiabilityRunDatasetGroup DG
	LEFT JOIN
	@LiabilityRunMemberDatasetGroup MDG ON MDG.DatasetGroupID = DG.ID

WHERE
	DG.GroupDefinitionID = 1338 AND DG.Number <> 0
ORDER BY
	DG.Number

Martin

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 07:18:27

And I think there should be some use of code similar to the below, but I could be wrong...


DG.Number = 0 AND COUNT(MDG.MemberID) > 0

Martin

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 07:25:38

This


SELECT 
	DG.ID,
	DG.Name,
	DG.Number
FROM
	@LiabilityRunDatasetGroup DG
	LEFT JOIN
	@LiabilityRunMemberDatasetGroup MDG ON MDG.DatasetGroupID = DG.ID

WHERE
	DG.GroupDefinitionID = 1338 AND DG.Number <> 0
	Group by
	DG.ID,
	DG.Name,
	DG.Number
	HAVING COUNT(MDG.MemberID)>0
ORDER BY
	DG.Number

PBUH

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 07:39:55

OK, almost there.
The DG.Number <> 0 excludes both the rejected groups and any other empty groups.
Remove 'AND DG.Number <> 0' and that includes non-empty rejected groups but excludes empty non-rejected groups.
Martin

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 07:47:06

Can you please post the resulset for DG.GroupDefinitionID = 1337 with the above sample data?

PBUH

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 09:15:37

Using the code in your previous post:
15857 Test Group 1 1
Whereas, it should actually be:
15857 Test Group 1 1
15857 Test Group 2 2
Thanks for your help so far - much appreciated
Martin

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 09:18:56

Sorted it based on your example:


SELECT 
	DG.ID,
	DG.Name,
	DG.Number
FROM
	@LiabilityRunDatasetGroup DG
	LEFT JOIN
	@LiabilityRunMemberDatasetGroup MDG ON MDG.DatasetGroupID = DG.ID

WHERE
	DG.GroupDefinitionID = 1338 
	GROUP BY
	DG.ID,
	DG.Name,
	DG.Number
	HAVING (DG.Number = 0 AND COUNT(MDG.MemberID) > 0) OR (DG.Number > 0)
ORDER BY
	DG.Number

Thanks again :)
Martin

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 09:23:40

This?


SELECT 
	DG.ID,
	DG.Name,
	DG.Number
FROM
	@LiabilityRunDatasetGroup DG
	LEFT JOIN
	@LiabilityRunMemberDatasetGroup MDG ON MDG.DatasetGroupID = DG.ID

WHERE
	DG.GroupDefinitionID = 1338 AND DG.Number <> 0
	Group by
	DG.ID,
	DG.Name,
	DG.Number
	HAVING COUNT(case when  DG.Number <> 0 then Dg.ID else 0 end)>0
ORDER BY
	DG.Number

Now if this also is not what you want then its time for me to go & put my head in sand.

PBUH

MSwanston
Starting Member

23 Posts

Posted - 2010-09-29 : 09:35:06

Hi
No, that doesn't work either, but my code in the post above does, which I couldn't have got to without yopur help, so no head in sand needed.
Thanks again
Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources