Finding unique values from an associative table.

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

Finding unique values from an associative table.

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2015-04-15 : 16:00:03

I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles... Can someone help?

USE [CGB]
GO
/****** Object:  Table [dbo].[ePID_BHID]    Script Date: 04/15/2015 15:48:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ePID_BHID](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BHID] [int] NULL,
	[ePID] [int] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ePID_BHID] ON
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258740, 1082464, 202545374)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258741, 1082464, 202545534)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258742, 1082464, 202545634)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258743, 1082464, 202553276)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258744, 1082464, 202553517)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258745, 1082464, 202553797)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258749, 1082311, 4721273)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258750, 1082311, 166769513)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258751, 1082311, 46738655)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258752, 1082311, 47526305)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258754, 1082307, 202524765)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258756, 1082262, 138743757)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258757, 1082262, 164856058)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258758, 1082262, 164810717)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258759, 1082262, 202524815)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258760, 1082262, 138755140)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258761, 1082262, 166724229)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258762, 1082262, 166744237)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258763, 1082262, 202482341)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258764, 1082262, 202524765)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258765, 1082262, 202542899)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258766, 1082262, 138755240)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258767, 1082262, 166675221)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258768, 1082262, 166723192)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258769, 1082262, 166744017)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258770, 1082262, 166769963)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258771, 1082262, 166786468)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258772, 1082262, 202482041)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258773, 1082262, 202482271)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258774, 1082262, 202482381)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258775, 1082262, 202482461)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258777, 1082313, 202545644)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258778, 1082313, 208531704)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258780, 1082310, 202524765)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258781, 1082310, 202545644)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258782, 1082310, 208531704)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258783, 1082308, 4721273)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258784, 1082308, 166769513)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258785, 1082308, 46738655)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258786, 1082308, 47526305)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258787, 1082307, 202545644)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258788, 1082307, 208531704)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258789, 1082311, 4721273)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258790, 1082311, 166769513)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258791, 1082311, 46738655)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258792, 1082311, 47526305)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258793, 1082310, 202524765)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258794, 1082310, 202545644)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258795, 1082310, 208531704)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258797, 1082308, 4721273)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258798, 1082308, 166769513)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258799, 1082308, 46738655)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258800, 1082308, 47526305)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258801, 1082307, 202524765)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258802, 1082307, 202545644)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258803, 1082307, 208531704)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258804, 1082305, 4721273)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258805, 1082305, 166769513)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258806, 1082305, 46738655)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258807, 1082305, 47526305)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258810, 1089992, 4721273)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258811, 1089992, 166769513)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258812, 1089992, 46738655)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258813, 1089992, 47526305)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258969, 1080919, 206053513)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258986, 1089898, 4954152)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (258987, 1089898, 4778055)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259001, 1089937, 138743757)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259002, 1089937, 164856058)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259003, 1089937, 164810717)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259004, 1089937, 202524815)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259005, 1089937, 138755140)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259006, 1089937, 166724229)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259007, 1089937, 166744237)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259008, 1089937, 202482341)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259009, 1089937, 202524765)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259010, 1089937, 202542899)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259011, 1089937, 138755240)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259012, 1089937, 166675221)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259013, 1089937, 166723192)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259014, 1089937, 166744017)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259015, 1089937, 166769963)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259016, 1089937, 166786468)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259017, 1089937, 202482041)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259018, 1089937, 202482271)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259019, 1089937, 202482381)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259020, 1089937, 202482461)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259022, 1080514, 4899079)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259028, 1080505, 4899079)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259042, 1080521, 133602721)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259043, 1080521, 30426592)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259048, 1080492, 133701333)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259054, 1089994, 4721273)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259055, 1089994, 166769513)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259056, 1089994, 46738655)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259057, 1089994, 47526305)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259058, 1089993, 164839813)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259059, 1089993, 138658131)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259060, 1089993, 166763540)
INSERT [dbo].[ePID_BHID] ([ID], [BHID], [ePID]) VALUES (259061, 1089993, 166700314)
GO

-Sergio
I use Microsoft SQL 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-15 : 16:08:29

WITH CTE (ePID)
AS (
SELECT ePID
FROM ePID_BHID
GROUP BY ePID
HAVING COUNT(*) = 1)
SELECT ePID, BHID
FROM ePID_BHID e
JOIN CTE c ON e.ePID = c.ePID

You can also use ROW_NUMBER() function for this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-15 : 16:09:23

So, using your sample data, what should the query return?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources