Query - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Query

Author

Topic

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-10 : 00:15:40

Hello,

Can anyone help me with the sql query? Below is the basic data I have (from "Employee" table). I want to display the output with all fields only for the account that appear more than one.

Here is the Select statement:
SELECT ID, FirstName, LastName, Class, Account, Station
FROM Employee

ID FirstName LastName Class Account Station
1 Andy Garcia A 1234-2354 Powell
2 Steve Smith B 1234-2354 Civic
3 Alex Good B 2335-5544 Main
4 Ryan Brown C 1255-6640 Labor
5 Jimmy Roy D 1255-6640 Mini
6 Luke Man D 1255-6640 Kilo

Thank you all

SQLBoy

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-09-10 : 01:25:19

[Code]
;WITH aCTE (ID,FirstName,LastName,Class,Account,Station)
AS
(SELECT 1,'Andy','Garcia','A','1234-2354','Powell' UNION ALL
SELECT 2,'Steve','Smith','B','1234-2354','Civic' UNION ALL
SELECT 3 ,'Andy','Garcia','A','1234-2354','Powell' )

SELECT
A.ID, A.FirstName,A.LastName,A.Class,A.Account,A.Station
FROM aCTE AS A
INNER JOIN
(SELECT
Account
FROM aCTE
GROUP BY Account
HAVING COUNT(Account)>1) B
ON A.Account = B.Account

[/code]

sabinWeb MCP

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-10 : 09:18:54

Hi Stepson,

Thank you for your response. So, in the Union, we are UNION ALL back to the first Statement? (SELECT 3, 'Andy', 'Garcia', 'A','1234-2354', 'Powell'). Now, if I have 15000 records/rows, how do I handle it in the query on the top section? In the Union Part?

SQLBoy

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-09-11 : 00:53:40

HI,

This piece of code


;WITH aCTE (ID,FirstName,LastName,Class,Account,Station)
AS
	(SELECT 1,'Andy','Garcia','A','1234-2354','Powell' UNION ALL
	SELECT 2,'Steve','Smith','B','1234-2354','Civic' UNION ALL
	SELECT 3 ,'Andy','Garcia','A','1234-2354','Powell' )

is your source table/view ...

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-09-11 : 01:07:00

I don't have your source, so I created one for it.
Your query should look like :


SELECT 
	A.ID, A.FirstName,A.LastName,A.Class,A.Account,A.Station
FROM YourSourceTAble AS A
	INNER JOIN
	(SELECT 
		Account
	 FROM YourSourceTAble
	 GROUP BY Account
	 HAVING COUNT(Account)>1) B
	 ON A.Account = B.Account

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources