Display one row over another if there are two

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
2
down vote

favorite

Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.

If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.

Any way to do that?

SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID
from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
 SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID

Example result set:

1234 John Williams Sacramento CA 1 
1234 John Williams Portland OR 2 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2

Would like this back:

1234 John Williams Sacramento CA 1 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2

edited 2 hours ago

MDCCL

6,30731740

asked 3 hours ago

Isaac Holmes

132

New contributor

I don't think you need that subquery in your where condition
â€“Â Mattia Nocerino
3 hours ago

1

Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
â€“Â RDFozz
2 hours ago

@MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
â€“Â Isaac Holmes
2 hours ago

1

So Address (ID) is not the primary key of Address. OK.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
1 hour ago

Â |Â
show 1 more comment

up vote
2
down vote

favorite

Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.

If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.

Any way to do that?

SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID
from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
 SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID

Example result set:

1234 John Williams Sacramento CA 1 
1234 John Williams Portland OR 2 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2

Would like this back:

1234 John Williams Sacramento CA 1 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2

edited 2 hours ago

MDCCL

6,30731740

asked 3 hours ago

Isaac Holmes

132

New contributor

I don't think you need that subquery in your where condition
â€“Â Mattia Nocerino
3 hours ago

1

Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
â€“Â RDFozz
2 hours ago

@MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
â€“Â Isaac Holmes
2 hours ago

1

So Address (ID) is not the primary key of Address. OK.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
1 hour ago

Â |Â
show 1 more comment

up vote
2
down vote

favorite

Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.

If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.

Any way to do that?

SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID
from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
 SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID

Example result set:

1234 John Williams Sacramento CA 1 
1234 John Williams Portland OR 2 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2

Would like this back:

1234 John Williams Sacramento CA 1 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2

edited 2 hours ago

MDCCL

6,30731740

asked 3 hours ago

Isaac Holmes

132

New contributor

Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.

If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.

Any way to do that?

SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID
from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
 SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID

Example result set:

1234 John Williams Sacramento CA 1 
1234 John Williams Portland OR 2 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2

Would like this back:

1234 John Williams Sacramento CA 1 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2

sql-server sql-server-2017

edited 2 hours ago

MDCCL

6,30731740

asked 3 hours ago

Isaac Holmes

132

New contributor

edited 2 hours ago

MDCCL

6,30731740

asked 3 hours ago

Isaac Holmes

132

New contributor

edited 2 hours ago

MDCCL

6,30731740

edited 2 hours ago

MDCCL

6,30731740

edited 2 hours ago

MDCCL

6,30731740

asked 3 hours ago

Isaac Holmes

132

New contributor

asked 3 hours ago

Isaac Holmes

132

asked 3 hours ago

Isaac Holmes

132

New contributor

Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

I don't think you need that subquery in your where condition
â€“Â Mattia Nocerino
3 hours ago

1

Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
â€“Â RDFozz
2 hours ago

@MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
â€“Â Isaac Holmes
2 hours ago

1

So Address (ID) is not the primary key of Address. OK.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
1 hour ago

Â |Â
show 1 more comment

I don't think you need that subquery in your where condition
â€“Â Mattia Nocerino
3 hours ago

1

Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
â€“Â RDFozz
2 hours ago

@MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
â€“Â Isaac Holmes
2 hours ago

1

So Address (ID) is not the primary key of Address. OK.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
1 hour ago

I don't think you need that subquery in your where condition
â€“Â Mattia Nocerino
3 hours ago

Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
â€“Â RDFozz
2 hours ago

@MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
â€“Â Isaac Holmes
2 hours ago

So Address (ID) is not the primary key of Address. OK.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
1 hour ago

Â |Â
show 1 more comment

3 Answers
3

active

oldest

votes

up vote
2
down vote

accepted

Wrap your query in a common table expression and add a row number column like this

SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
Row_Number() over(partition by Employees.ID order by Site.id) as rn

Then, select from the common table expression where rn = 1

answered 2 hours ago

Scott Hodgin

15.6k11535

After a little more research and leveling up the SQL query skills this worked great. Thanks.
â€“Â Isaac Holmes
28 mins ago

@IsaacHolmes - Awesome, glad it worked for you!
â€“Â Scott Hodgin
27 mins ago

add a commentÂ |Â

up vote
1
down vote

This can be achieved by joining the table Site twice:

SELECT e.ID
 , e.FName
 , e.LName
 , e.City
 , a.State
 , s.ID
 FROM Employees AS e
 JOIN Address AS a ON a.ID = e.ID
 JOIN Site AS s ON s.ID = a.ADID
 LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
 AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
 WHERE s.Type = 'Primary'
 AND z.ID IS NULL 
 ORDER BY e.ID ASC
;

edited 2 hours ago

answered 3 hours ago

Kondybas

2,11789

Very nice working in-all-database solution.
â€“Â Luciano Andress Martini
3 hours ago

1

Duplicate commas detected ;)
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

1

@ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹ Yep you are absolutely right!
â€“Â Kondybas
2 hours ago

add a commentÂ |Â

up vote
0
down vote

SELECT * FROM (
 SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
 Row_Number() over(partition by Employees.ID order by Site.ID) as RN
 from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID 
 and Site.Type = 'Primary'
) as [AllEmpTable]
Where [AllEmpTable].RN = 1
Order by Employees.ID, Site.ID

answered 24 mins ago

Isaac Holmes

132

New contributor

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

Isaac Holmes is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218628%2fdisplay-one-row-over-another-if-there-are-two%23new-answer', 'question_page');

);

Post as a guest

Name

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
2
down vote

accepted

Wrap your query in a common table expression and add a row number column like this

SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
Row_Number() over(partition by Employees.ID order by Site.id) as rn

Then, select from the common table expression where rn = 1

answered 2 hours ago

Scott Hodgin

15.6k11535

After a little more research and leveling up the SQL query skills this worked great. Thanks.
â€“Â Isaac Holmes
28 mins ago

@IsaacHolmes - Awesome, glad it worked for you!
â€“Â Scott Hodgin
27 mins ago

add a commentÂ |Â

up vote
2
down vote

accepted

Wrap your query in a common table expression and add a row number column like this

SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
Row_Number() over(partition by Employees.ID order by Site.id) as rn

Then, select from the common table expression where rn = 1

answered 2 hours ago

Scott Hodgin

15.6k11535

After a little more research and leveling up the SQL query skills this worked great. Thanks.
â€“Â Isaac Holmes
28 mins ago

@IsaacHolmes - Awesome, glad it worked for you!
â€“Â Scott Hodgin
27 mins ago

add a commentÂ |Â

up vote
2
down vote

accepted

Wrap your query in a common table expression and add a row number column like this

SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
Row_Number() over(partition by Employees.ID order by Site.id) as rn

Then, select from the common table expression where rn = 1

answered 2 hours ago

Scott Hodgin

15.6k11535

Wrap your query in a common table expression and add a row number column like this

SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
Row_Number() over(partition by Employees.ID order by Site.id) as rn

Then, select from the common table expression where rn = 1

answered 2 hours ago

Scott Hodgin

15.6k11535

answered 2 hours ago

Scott Hodgin

15.6k11535

answered 2 hours ago

Scott Hodgin

15.6k11535

answered 2 hours ago

Scott Hodgin

15.6k11535

After a little more research and leveling up the SQL query skills this worked great. Thanks.
â€“Â Isaac Holmes
28 mins ago

@IsaacHolmes - Awesome, glad it worked for you!
â€“Â Scott Hodgin
27 mins ago

add a commentÂ |Â

After a little more research and leveling up the SQL query skills this worked great. Thanks.
â€“Â Isaac Holmes
28 mins ago

@IsaacHolmes - Awesome, glad it worked for you!
â€“Â Scott Hodgin
27 mins ago

After a little more research and leveling up the SQL query skills this worked great. Thanks.
â€“Â Isaac Holmes
28 mins ago

@IsaacHolmes - Awesome, glad it worked for you!
â€“Â Scott Hodgin
27 mins ago

add a commentÂ |Â

up vote
1
down vote

This can be achieved by joining the table Site twice:

SELECT e.ID
 , e.FName
 , e.LName
 , e.City
 , a.State
 , s.ID
 FROM Employees AS e
 JOIN Address AS a ON a.ID = e.ID
 JOIN Site AS s ON s.ID = a.ADID
 LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
 AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
 WHERE s.Type = 'Primary'
 AND z.ID IS NULL 
 ORDER BY e.ID ASC
;

edited 2 hours ago

answered 3 hours ago

Kondybas

2,11789

Very nice working in-all-database solution.
â€“Â Luciano Andress Martini
3 hours ago

1

Duplicate commas detected ;)
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

1

@ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹ Yep you are absolutely right!
â€“Â Kondybas
2 hours ago

add a commentÂ |Â

up vote
1
down vote

This can be achieved by joining the table Site twice:

SELECT e.ID
 , e.FName
 , e.LName
 , e.City
 , a.State
 , s.ID
 FROM Employees AS e
 JOIN Address AS a ON a.ID = e.ID
 JOIN Site AS s ON s.ID = a.ADID
 LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
 AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
 WHERE s.Type = 'Primary'
 AND z.ID IS NULL 
 ORDER BY e.ID ASC
;

edited 2 hours ago

answered 3 hours ago

Kondybas

2,11789

Very nice working in-all-database solution.
â€“Â Luciano Andress Martini
3 hours ago

1

Duplicate commas detected ;)
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

1

@ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹ Yep you are absolutely right!
â€“Â Kondybas
2 hours ago

add a commentÂ |Â

up vote
1
down vote

This can be achieved by joining the table Site twice:

SELECT e.ID
 , e.FName
 , e.LName
 , e.City
 , a.State
 , s.ID
 FROM Employees AS e
 JOIN Address AS a ON a.ID = e.ID
 JOIN Site AS s ON s.ID = a.ADID
 LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
 AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
 WHERE s.Type = 'Primary'
 AND z.ID IS NULL 
 ORDER BY e.ID ASC
;

edited 2 hours ago

answered 3 hours ago

Kondybas

2,11789

This can be achieved by joining the table Site twice:

SELECT e.ID
 , e.FName
 , e.LName
 , e.City
 , a.State
 , s.ID
 FROM Employees AS e
 JOIN Address AS a ON a.ID = e.ID
 JOIN Site AS s ON s.ID = a.ADID
 LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
 AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
 WHERE s.Type = 'Primary'
 AND z.ID IS NULL 
 ORDER BY e.ID ASC
;

edited 2 hours ago

answered 3 hours ago

Kondybas

2,11789

edited 2 hours ago

answered 3 hours ago

Kondybas

2,11789

answered 3 hours ago

Kondybas

2,11789

answered 3 hours ago

Kondybas

2,11789

Very nice working in-all-database solution.
â€“Â Luciano Andress Martini
3 hours ago

1

Duplicate commas detected ;)
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

1

@ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹ Yep you are absolutely right!
â€“Â Kondybas
2 hours ago

add a commentÂ |Â

Very nice working in-all-database solution.
â€“Â Luciano Andress Martini
3 hours ago

1

Duplicate commas detected ;)
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

1

@ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹ Yep you are absolutely right!
â€“Â Kondybas
2 hours ago

Very nice working in-all-database solution.
â€“Â Luciano Andress Martini
3 hours ago

Duplicate commas detected ;)
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
â€“Â ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹
2 hours ago

@ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹ Yep you are absolutely right!
â€“Â Kondybas
2 hours ago

add a commentÂ |Â

up vote
0
down vote

SELECT * FROM (
 SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
 Row_Number() over(partition by Employees.ID order by Site.ID) as RN
 from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID 
 and Site.Type = 'Primary'
) as [AllEmpTable]
Where [AllEmpTable].RN = 1
Order by Employees.ID, Site.ID

answered 24 mins ago

Isaac Holmes

132

New contributor

add a commentÂ |Â

up vote
0
down vote

SELECT * FROM (
 SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
 Row_Number() over(partition by Employees.ID order by Site.ID) as RN
 from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID 
 and Site.Type = 'Primary'
) as [AllEmpTable]
Where [AllEmpTable].RN = 1
Order by Employees.ID, Site.ID

answered 24 mins ago

Isaac Holmes

132

New contributor

add a commentÂ |Â

up vote
0
down vote

SELECT * FROM (
 SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
 Row_Number() over(partition by Employees.ID order by Site.ID) as RN
 from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID 
 and Site.Type = 'Primary'
) as [AllEmpTable]
Where [AllEmpTable].RN = 1
Order by Employees.ID, Site.ID

answered 24 mins ago

Isaac Holmes

132

New contributor

SELECT * FROM (
 SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
 Row_Number() over(partition by Employees.ID order by Site.ID) as RN
 from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID 
 and Site.Type = 'Primary'
) as [AllEmpTable]
Where [AllEmpTable].RN = 1
Order by Employees.ID, Site.ID

answered 24 mins ago

Isaac Holmes

132

New contributor

answered 24 mins ago

Isaac Holmes

132

New contributor

answered 24 mins ago

Isaac Holmes

132

answered 24 mins ago

Isaac Holmes

132

New contributor

Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a commentÂ |Â

Isaac Holmes is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Isaac Holmes is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky