CODEWITHSUNDEEP
sqlsql-serverselectsubquery
FatBoySlim7
FatBoySlim7

Reputation: 232

SQL Server query assistance

I have two tables that I'm trying to JOIN together. In table TblClient1 I have a ClientID with a bunch of client information - such as DOB, Names, Address and so forth.

In the other table TblClient2 I again have a clientID - but here is client's department - where they work (it's an DeptID that I'm trying to get).

The problem I'm facing is that each one of the tables has multiple instances of the same client. So in tblClient2, I can have up to 2 instances of the same client, and the same with tblClient2 (here I can have even 20 instances, but with the same DeptID in each case).

All in all I'm trying to link tblClient1 and tblClient2 by ClientID and in my results see ClientID and DeptID (only one instance).

Any and all help much appreciated.

Upvotes: 1

Views: 43

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Since the DeptID is always the same, you can use MAX() or MIN() to return only one.

SELECT T1.ClientID, MAX(DeptID)
FROM TblClient T1
INNER JOIN TblClient T2 on T1.ClientID = T2.ClientID
GROUP BY T1.ClientID

Upvotes: 3

Related Questions