HELP WITH A SELECT - 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

HELP WITH A SELECT

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-05-05 : 10:17:51

I have a table like:

CREATE TABLE #temp (EmpID int, code varchar(5))

INSERT INTO #temp
SELECT 111, 'A500'UNION ALL
SELECT 111, 'A200' UNION ALL
SELECT 111, 'A700' UNION ALL
SELECT 111, 'A900' UNION ALL
SELECT 222, 'A500' UNION ALL
SELECT 222, 'A200' UNION ALL
SELECT 222, 'A900' UNION ALL
SELECT 333, 'A700' UNION ALL
SELECT 333, 'A500' UNION ALL
SELECT 333, 'A900' UNION ALL
SELECT 444, 'A700' UNION ALL
SELECT 444, 'A200' UNION ALL
SELECT 444, 'A600'UNION ALL
SELECT 555, 'A800' UNION ALL
SELECT 666, 'A900'
Now I need to return employeeID with their codes.

But the problem is most employees will have different codes and all of them are to be selected but some will also have 3 codes A200, A500 or A700 pluse others and only 1 will have to selected from these 3 and rest of the codes of the employee

So minimum of A200, A500 and A700 are to be selected along with other codes.

If that is the case then minimum code is to be selected.

So for
Emp 111 A200, A900 is to be selected.
Emp 222 A200, A900 is to be selected
Emp 333 A500 and A900 to be selected
Emp 444 A200, A600 to be selected.

Any help will be appreciated. Thanks

-----------------------------------------------------------------------------------------------
Ashley Rhodes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-05 : 10:23:28

[code]DECLARE @Sample TABLE(EmpID INT, Code VARCHAR(5))

INSERT @Sample
SELECT 111, 'A500' UNION ALL
SELECT 111, 'A200' UNION ALL
SELECT 111, 'A700' UNION ALL
SELECT 111, 'A900' UNION ALL
SELECT 222, 'A500' UNION ALL
SELECT 222, 'A200' UNION ALL
SELECT 222, 'A900' UNION ALL
SELECT 333, 'A700' UNION ALL
SELECT 333, 'A500' UNION ALL
SELECT 333, 'A900' UNION ALL
SELECT 444, 'A700' UNION ALL
SELECT 444, 'A200' UNION ALL
SELECT 444, 'A600'UNION ALL
SELECT 555, 'A800' UNION ALL
SELECT 666, 'A900'

SELECT EmpID
FROM @Sample
WHERE Code IN ('A200', 'A500', 'A700')
GROUP BY EmpID
HAVING COUNT(DISTINCT Code) = 3
ORDER BY EmpID[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 10:57:41

[code]SELECT *
FROM
(
SELECT EmpID,MIN(Code) AS Code
FROM Table
WHERE Code IN ('A200', 'A500', 'A700')
GROUP BY EmpID

UNION ALL

SELECT EmpID,Code
FROM Table
WHERE Code NOT IN ('A200', 'A500', 'A700')
)t
ORDER BY t.EmpID
[/code]

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-05-05 : 11:55:36

there cannot be a union or union all
as there are lot of other joins and conditions
and also
Peso your code returns only 1 row

-----------------------------------------------------------------------------------------------
Ashley Rhodes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 11:58:11

quote:
Originally posted by ashley.sql

there cannot be a union or union all
as there are lot of other joins and conditions
and also
Peso your code returns only 1 row

-----------------------------------------------------------------------------------------------
Ashley Rhodes

didnt understand that. Can you show your full query?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-05 : 13:41:08

[code]DECLARE @Sample TABLE(EmpID INT, Code VARCHAR(5))

INSERT @Sample
SELECT 111, 'A500' UNION ALL
SELECT 111, 'A200' UNION ALL
SELECT 111, 'A700' UNION ALL
SELECT 111, 'A900' UNION ALL
SELECT 222, 'A500' UNION ALL
SELECT 222, 'A200' UNION ALL
SELECT 222, 'A900' UNION ALL
SELECT 333, 'A700' UNION ALL
SELECT 333, 'A500' UNION ALL
SELECT 333, 'A900' UNION ALL
SELECT 444, 'A700' UNION ALL
SELECT 444, 'A200' UNION ALL
SELECT 444, 'A600'UNION ALL
SELECT 555, 'A800' UNION ALL
SELECT 666, 'A900'

SELECT DISTINCT
S.EmpID,
STUFF(
(
SELECT DISTINCT TOP 100 PERCENT
', ' + S2.Code
FROM
(
SELECT EmpID, Code
FROM @Sample
WHERE Code NOT IN ('A200', 'A500', 'A700')

UNION ALL

SELECT EmpID, MIN(Code)
FROM @Sample
WHERE Code IN ('A200', 'A500', 'A700')
GROUP BY EmpID
) AS S2
WHERE
S2.EmpID = S.EmpID
ORDER BY
', ' + S2.Code
FOR XML PATH('')
), 1, 1, '') AS Codes
FROM
@Sample AS S[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources