FatBoySlim7 FatBoySlim7 - 7 months ago 19
SQL Question

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.

Answer

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