Getting one record from group - 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

Getting one record from group

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2012-06-21 : 16:13:24

I have an account ID that has records that are linked to multiple categories in one group. I'm trying to find any account ID who is not a member of a certain group called SPGS-Masters Alumni. Any account that has a certain degree ID as well.

SELECT TOP (100) PERCENT dbo.Account.AccountID, dbo.Account.FirstName, dbo.Account.LastName, dbo.AccountGroup.GroupCode, dbo.Group_.Group_,

dbo.Degree.Degree

FROM dbo.Account INNER JOIN

dbo.AccountGroup ON dbo.Account.AccountID = dbo.AccountGroup.AccountID INNER JOIN

dbo.Education ON dbo.Account.AccountID = dbo.Education.AccountID INNER JOIN

dbo.Degree ON dbo.Education.DegreeCode = dbo.Degree.DegreeCode LEFT OUTER JOIN

dbo.Group_ ON dbo.AccountGroup.GroupCode = dbo.Group_.GroupCode

WHERE Degree.Degree LIKE 'm%' AND Group_ <> 'SPGS-Masters Alumni' and dbo.Degree.Degree in ('MAED', 'MBA', 'MASL')
ORDER BY dbo.Account.AccountID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-21 : 16:52:59

Hard to say without knowing you schema or data. Can you just move the Group predicate out of the where clause and onto the join?

SELECT TOP (100) PERCENT 
	dbo.Account.AccountID, 
	dbo.Account.FirstName, 
	dbo.Account.LastName, 
	dbo.AccountGroup.GroupCode, 
	dbo.Group_.Group_, 
	dbo.Degree.Degree
FROM 
	dbo.Account 
INNER JOIN
	dbo.AccountGroup 
	ON dbo.Account.AccountID = dbo.AccountGroup.AccountID 
INNER JOIN
	dbo.Education 
	ON dbo.Account.AccountID = dbo.Education.AccountID 
INNER JOIN
	dbo.Degree 
	ON dbo.Education.DegreeCode = dbo.Degree.DegreeCode 
LEFT OUTER JOIN
	dbo.Group_ 
	ON dbo.AccountGroup.GroupCode = dbo.Group_.GroupCode
	AND Group_.Group_ <> 'SPGS-Masters Alumni' 
WHERE 
	Degree.Degree LIKE 'm%' 
	AND dbo.Degree.Degree in ('MAED', 'MBA', 'MASL')
ORDER BY 
	dbo.Account.AccountID

If that is not what you want post some DDL, DML and expected output so we can help:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
http://www.sqlservercentral.com/articles/Best+Practices/61537/

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2012-06-21 : 17:22:34

Account ID=3
Last Name= Whitney
Group_= Alumni, Identified and SPGS-Masters Alumni
Degree=MAED

Account ID=1
LastName= Smith
Group_= Alumni and Identified
Degree=MBA

I want to pull a list of names with criteria similar to account ID 1 I only want the people who are not in the Group_ SPGS-Masters Alumni.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 17:44:09

SELECT * FROM table WHERE Group_ NOT LIKE '% SPGS-Masters Alumni %'

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

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2012-06-22 : 09:49:16

Both of these responses are returning results such as this:

Account ID=3
Group_ = Alumni
Degree=MAED

So then it appears that Account ID 3 is not in the Group_ SPGS_Masters Alumni when in fact it is. I need to identify anyone that is not in the group so that I can generate a list of folks who need to be in SPGS_Masters Alumni.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-22 : 11:18:45

Quoting myself to see if it'll help:

quote:
Originally posted by Lamprey

If that is not what you want post some DDL, DML and expected output so we can help:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
http://www.sqlservercentral.com/articles/Best+Practices/61537/

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2012-06-22 : 11:57:13

I guess I'm not sure what additional information you need. The DDL is in the first post and I Thought I was pretty clear about my expected output.

Account ID LastName Group_ Degree
3 Whitney SPGS-Masters Alumni MAED
3 Whitney Alumni MAED
1 Smith Alumni MBA
1 Snmith Identified MBA

So, in this case I would Want a query that only returns the output from Account number 1 because he is not in the group SPGS-masters alumni.

SELECT TOP (100) PERCENT dbo.Account.AccountID, dbo.Account.FirstName, dbo.Account.LastName, dbo.AccountGroup.GroupCode, dbo.Group_.Group_,

dbo.Degree.Degree

FROM dbo.Account INNER JOIN

dbo.AccountGroup ON dbo.Account.AccountID = dbo.AccountGroup.AccountID INNER JOIN

dbo.Education ON dbo.Account.AccountID = dbo.Education.AccountID INNER JOIN

dbo.Degree ON dbo.Education.DegreeCode = dbo.Degree.DegreeCode LEFT OUTER JOIN

dbo.Group_ ON dbo.AccountGroup.GroupCode = dbo.Group_.GroupCode

WHERE (dbo.Degree.Degree LIKE 'm%')

ORDER BY dbo.Account.AccountID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 22:31:45

[code]
SELECT dbo.Account.AccountID, dbo.Account.FirstName, dbo.Account.LastName, dbo.AccountGroup.GroupCode, dbo.Group_.Group_, dbo.Degree.Degree
INTO #temp
FROM dbo.Account INNER JOIN
dbo.AccountGroup ON dbo.Account.AccountID = dbo.AccountGroup.AccountID INNER JOIN

dbo.Education ON dbo.Account.AccountID = dbo.Education.AccountID INNER JOIN

dbo.Degree ON dbo.Education.DegreeCode = dbo.Degree.DegreeCode LEFT OUTER JOIN

dbo.Group_ ON dbo.AccountGroup.GroupCode = dbo.Group_.GroupCode

WHERE (dbo.Degree.Degree LIKE 'm%')

SELECT t.*
FROM #temp t
WHERE NOT EXISTS (SELECT 1 FROM #temp WHERE [Account ID] = t.[Account ID] AND Group_ LIKE '% SPGS-Masters Alumni %')
ORDER BY AccountID

DROP TABLE #Temp
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources