• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 66
  • Last Modified:

return uses with multiple records from sql server table

I'm using sql server 2008

I have a table called TestEmployees1

It looks like this:

table
This is the script to create the table:

USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestEmployees1](
	[Region] [int] NULL,
	[RegionName] [nvarchar](255) NULL,
	[EmployeeID] [int] NULL,
	[LastName] [nvarchar](255) NULL,
	[FirstName] [nvarchar](255) NULL,
	[MidInit] [nvarchar](255) NULL,
	[HoursWorked] [float] NULL,
	[StatusFlag] [float] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (1, N'New York', 1, N'Johnson', N'Bill', N'A', 55, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (18, N'California', 1, N'Johnson', N'Bill', N'A', 325, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (21, N'Florida', 2, N'Williams', N'John', N' ', 5, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (49, N'Texas', 2, N'Williams', N'John', N' ', 412, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (1, N'New York', 3, N'Smith', N'Ally', N'V', 40.5, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (74, N'Michigan', 3, N'Smith', N'Ally', N'V', 7.5, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (650, N'Oregon', 4, N'Simpson', N'Bart', N'N', 1224, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (650, N'Oregon', 5, N'Simpson', N'Homer', N'R', 1224, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (150, N'Nevada', 6, N'Jackson', N'Joe', N' ', 888, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (22, N'Ohio', 7, N'Kross', N'Chris', N'W', 105, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (150, N'Nevada', 8, N'Smith', N'Michelle', N'R', 744, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (150, N'Nevada', 9, N'Holly', N'Michelle', N'D', 36, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (170, N'Washington', 10, N'Lee', N'Brenda', N' ', 32, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (150, N'Nevada', 11, N'Cowell', N'Simon', N'E', 807, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (22, N'Ohio', 12, N'Martin', N'Gus', N'E', 114.5, 0)
GO

Open in new window


I'm trying to write a query to query this table that will do the following.

Return all employees who work in more than 1 region.
So if you look at the table the only employees who work in more than 1 region are
Bill Johnson, John Williams and Ally Smith.

My desired result looks like this:

desired result
I was thinking of using Having Count > 1 but not sure if that is the best to use.

Anyone know a good query to get this result?
0
maqskywalker
Asked:
maqskywalker
  • 2
1 Solution
 
Jan LouwerensSoftware EngineerCommented:
You could use an analytic function.

SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag] FROM
(
   SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag], COUNT(*) OVER (PARTITION BY [EmployeeID]) AS Total FROM [TestEmployees1]
)
WHERE
   Total > 1
ORDER BY [EmployeeID], [Region]

Open in new window




This would be similar to the HAVING clause you mentioned:
SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag] FROM [TestEmployees1] WHERE
   [EmployeeID] IN
   (
      SELECT [EmployeeID] FROM
      (
         SELECT [EmployeeID], COUNT(*) AS Regions FROM [TestEmployees1]
         GROUP BY [EmployeeID]
         HAVING COUNT(*) > 1
      )
      WHERE
         Total_Regions > 1
   )
ORDER BY [EmployeeID], [Region]

Open in new window

0
 
maqskywalkerAuthor Commented:
Thanks that worked.

http://sqlfiddle.com/#!18/d85cc/5
0
 
Jan LouwerensSoftware EngineerCommented:
Ah, it looks like you have to alias the inner query:

SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag] FROM
(
   SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag], COUNT(*) OVER (PARTITION BY [EmployeeID]) AS Total FROM [TestEmployees1]
) Inner_Query
WHERE
   Total > 1
ORDER BY [EmployeeID], [Region]

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now