Getting data based on group

Question

Getting data based on group

prefet

SSC-Addicted

Points: 492
More actions
March 3, 2017 at 3:35 am

#322267

First post so apologies for the etiquette.
I'm trying to retrieve some data that is missing by using it at the group level.
My example below :
CREATE TABLE GuidTable     (id int,     guidID varchar(5),     guidREP varchar(10));
INSERT INTO GuidTable (id, guidID, guidREP)     VALUES ('1', 'guid1', 'Alex'),          ('2','guid2','John'),          ('3','guid3',NULL),          ('4','guid4',NULL),          ('5','guid5','Stacey');
CREATE TABLE GuidGroup     (id int,      groupname varchar(6),      guidID varchar(5));
INSERT INTO GuidGroup(id, groupname, guidID)     VALUES ('1','group1','guid1'),          ('2','group1','guid3'),          ('3','group2','guid2'),          ('4','group2','guid4'),          ('5','group3','guid5');
The result I'm looking for is the following. Any ideas how to achieve that ?

Thanks.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

Thanks for providing DDL and sample data. Can you explain why group 1 returns Alex twice, and group 2 John? What is your logic?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

prefet SSC-Addicted Points: 492 More actions · Answer 2

Looking at group1, it has the guid1 and guid3 associated it. If I look at the GuidTable I can check that guid1 has Alex but guid3 has NULL. The logic is that the GuidTable should have Alex for both guid1 and guid3 since they are both part of group1. Basically if there's a NULL value, I'd like to check if any of the other guid's that are part of the same group have a name associated to it and if so then pickup that name.

Thom A SSC Guru Points: 99143 More actions · Answer 3

Ok, what would you return if a GROUP had 3 GUID's related to it, of which 1 had a value of NULL?

Say for example Guid1 had a value of "Alex", Guid2 a value of "Steve", and Guid3 had a value of NULL. Would the 3rd Guid return Alex, or Steve?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

prefet SSC-Addicted Points: 492 More actions · Answer 4

The way my data is structured one group only has one name associated to it, spread across multiple guid's. So group 1 could have 18 guid's which should all have Alex associated to it but some might happen to be NULL. If so then it should check any of the other guid's and find Alex.
For your example, if Guid3 has NULL, then it needs to check back to the GuidGroup table, find the group Guid3 belongs to, in this case group1 and so return Alex. Agreed this is not very SQL'y thinking, as I'm coming from Python...

Thom A SSC Guru Points: 99143 More actions · Answer 5

The following works:
WITH Groups AS( SELECT GG.groupname, GT.guidREP, GG.guidID FROM GuidGroup GG JOIN GuidTable GT ON GG.guidID = GT.guidID) SELECT G.groupname, ISNULL(G.guidRep,GG.guidREP) AS guidRep FROM Groups G OUTER APPLY (SELECT TOP 1 * FROM Groups oa WHERE oa.groupname = G.groupname AND oa.guidREP IS NOT NULL AND G.guidREP IS NULL ORDER BY oa.guidID) GG ORDER BY groupname, guidRep;

Your data model, however, does puzzle me. If a group can only ever be assigned one person, i don't really understand why it can have multiple of the same people.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

prefet SSC-Addicted Points: 492 More actions · Answer 6

Brilliant. Thanks a ton. Perhaps I've oversimplified my example which makes the data model unclear also I think the data hasn't been properly loaded which leaves NULL's in place where they shouldn't be.