sql - retrieve only first record using join

Question

I have three tables: Employee, Contact, Employee_Contact

Employee:

EMP_ID, FIRST_NAME, LAST_NAME...

CONTACT:

CONTACT_ID, TELEPHONE, MOBILE...

EMPLOYEE_CONTACT:

EMP_ID, CONTACT_ID

Employee and Contact table is mapped with Employee_Contact table. So I want to retrieve all Employees with Employee's only first contact, I tried this query but it is retrieving all the contacts related to that employee. Please check below query and let me know in case of any mistakes.

SELECT EMP.FirstName
    ,DEPT.Description AS Department
    ,CNT.Mobile
    ,CNT.Telephone
FROM MstEmp EMP
LEFT OUTER JOIN Department DEPT ON EMP.DeptID = DEPT.Department_ID
LEFT OUTER JOIN Employee_Contact EC ON EMP.EMP_ID = EC.EMP_ID
OUTER APPLY (
    SELECT TOP 1 *
    FROM Contact CONT
    WHERE EC.Contact_ID = CONT.Contact_ID
    ) CNT

ROWNUMBER() OVER partition will do that job – Anton Jan 11 '18 at 05:33 — Anton, Jan 11 '18 at 05:33

score 0 · Answer 1 · answered Jan 11 '18 at 05:25

0

Use Top keyword retrieve top one record.

SELECT TOP 1 EMP.FirstName, DEPT.Description AS Department, CNT.Mobile, 
CNT.Telephone
FROM MstEmp EMP
LEFT OUTER JOIN Department DEPT on EMP.DeptID = DEPT.Department_ID
LEFT OUTER JOIN Employee_Contact EC ON EMP.EMP_ID = EC.EMP_ID
OUTER APPLY(SELECT TOP 1 * FROM Contact CONT WHERE EC.Contact_ID = 
CONT.Contact_ID)CNT

answered Jan 11 '18 at 05:25

VinuBibin

679
8
19

I tried this but it is retrieving only first employee, I want all employees with only first contact – Shreyas Pednekar Jan 11 '18 at 05:30
You can add contact as well in the join rt? and pick the top one – VinuBibin Jan 11 '18 at 05:37
Tried it, still it is retrieving only first employee – Shreyas Pednekar Jan 11 '18 at 05:39
Please check thus link https://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row – VinuBibin Jan 11 '18 at 06:17

score 0 · Answer 2 · answered Jan 11 '18 at 05:32

Try this one:

SELECT EMP.FirstName
    ,DEPT.Description AS Department
    ,t.Mobile
    ,t.Telephone
FROM MstEmp EMP
LEFT OUTER JOIN Department DEPT ON EMP.DeptID = DEPT.Department_ID
LEFT OUTER JOIN Employee_Contact EC ON EMP.EMP_ID = EC.EMP_ID
LEFT OUTER JOIN Contact CONT ON EC.Contact_ID = (
        SELECT MIN(c.Contact_ID) c_id
        FROM Contact c
        WHERE CONT.Contact_ID = c.Contact_ID
        ) t

Jibin Balachandran · Answer 3 · 2018-01-11T08:32:00.340

0

You can use ROW_NUMBER() and get the first record for all the employees and then join it with the other tables.

SELECT EMP.FirstName,
       DEPT.Description AS Department,
       c.Mobile,
       c.Telephone
FROM MstEmp EMP
LEFT OUTER JOIN Department DEPT ON EMP.DeptID = DEPT.Department_ID
INNER JOIN (
             SELECT *,ROW_NUMBER() OVER (PARTITION BY EMP_ID ORDER BY EMP_ID) AS RN
             FROM Employee_Contact  ) EC ON EMP.EMP_ID = EC.EMP_ID
INNER JOIN Contact c ON c.Contact_ID=EC.Contact_ID
WHERE EC.RN=1

edited Jan 11 '18 at 08:32

answered Jan 11 '18 at 05:50

Jibin Balachandran

3,381
1
24
38

It is giving my an error "The multi-part identifier "EMP.EMP_ID" could not be bound." anyway, thank you :) – Shreyas Pednekar Jan 11 '18 at 07:20
@ShreyasPednekar Updated my answer, have a look. – Jibin Balachandran Jan 11 '18 at 08:32

Michał Turczyn · Answer 4 · 2018-01-11T07:50:44.157

0

Give it a try:

select * from (
select E.[emp_id],
       E.[first_name], 
       E.[last_name], 
       C.*, 
       RANK() over (partition by (E.[emp_id], E.[first_name], E.[last_name]) order by (select null)) [RN]
from Employee [E]
join employee_contact [EC] on E.[emp_id] = EC.[emp_id]
join contact [C] on EC.[contact_id] = C.[contact_id]
) A where RN = 1

edited Jan 11 '18 at 07:50

answered Jan 11 '18 at 06:29

Michał Turczyn

32,028
14
47
69

It is giving an error "Incorrect syntax near the keyword 'by'." – Shreyas Pednekar Jan 11 '18 at 07:24
@ShreyasPednekar Try now – Michał Turczyn Jan 11 '18 at 07:25
@ShreyasPednekar How about now? – Michał Turczyn Jan 11 '18 at 07:50

sql - retrieve only first record using join

4 Answers4