Show all the records in one table and only matching records in second?

Transact-SQL
1

Hello, again,

Hopefully, this is an easier solution.

I have the following two tables:

CREATE TABLE [dbo].[Directors](
[DeptNum] nvarchar NULL,
[DeptName] nvarchar NULL,
[EmpName] nvarchar NULL,
[DirectorID] nvarchar NULL

CREATE TABLE [dbo].[EmployeeTable](
[Department] nvarchar NULL,
[DeptNum] nvarchar NULL,
[EmpName] nvarchar NULL,
[EmpNum] nvarchar NOT NULL,
[State] nvarchar NULL,
[City] nvarchar NULL,
[Zip] nvarchar NULL,
[Email] nvarchar NULL

The table called Directors displays all Directors and the departments they are responsible for.
A Director can be responsible for one or more departments.

Then the second table called EmployeeTable contains records of ALL employees and Directors are also employees on this table and there is one record for each employee based on employee id called empnum.
Employee Id on Directors table is called DirectorID and it is a foreign key to employeeTable table based on empnum.

What I have been trying to do is display a Director's record by joining the two tables.

If a Director, as indicated earlier, has more than one department in Directors table, display that Director's record with those departments associated with the Director.

I have attempted LEFT JOIN but I just keep getting only one department.

What am I doing wrong?

Thanks a lot in advance.

select h.DirectorID,
       e.DeptNum, 
	   e.Department,
	   e.EmpName as EmployeeName,
	   e.empnum,  
       e.Email,
	   e.zip
FROM EmployeeTable e  
LEFT JOIN Directors h on e.empnum = h.directorId  
WHERE h.directorid = '9884476'  
AND e.Password = 'T400s'
  1. List item
2

can you post some sample data

3

You need LEFT OUTER JOIN.

4

How about:

select h.DirectorID,
       e.DeptNum, 
	   e.Department,
	   e.EmpName as EmployeeName,
	   e.empnum,  
       e.Email,
	   e.zip
FROM EmployeeTable e  
LEFT JOIN Directors h on e.empnum = h.directorId  
                   AND e.Password = 'T400s'
WHERE h.directorid = '9884476'
5

I am sorry djj55 but I don't see the difference between your query and mine but in any case, I get the same results as mine.

Your query shows Jane Doe three times just as mine which is correct except that it shows the same department three times.

Here is some sample data if it helps:

For Directors table:

DeptNum	        DeptName	EmpName	        DirectorID	
750	           HR	        Doe, Jane	9884476	
755	        Administration	Doe, Jane	9884476	
803	       Veterans Affairs	Doe, Jane	9884476

For EmployeeTable table

    DeptNum	Department	Empname	       Empnum	Email	                 Zip
   750	        HR	       Doe, Jane	9884476	Jane.doe@yahoo.com	70612

So, no matter what I do, the only table that gets displayed is the HR on EmployeeTable.

6

Sorry I made a mistake. I was trying to limit Directors at the join and grabbed the EmployeeTable criteria. Please try.

select h.DirectorID,
       e.DeptNum, 
	   e.Department,
	   e.EmpName as EmployeeName,
	   e.empnum,  
       e.Email,
	   e.zip
FROM EmployeeTable e  
LEFT JOIN Directors h on e.empnum = h.directorId  
                   AND h.directorid = '9884476'
WHERE e.Password = 'T400s'
7

This one worked for me:

select h.DirectorID,
       e.DeptNum, 
	   h.DeptName,
	   e.EmpName as EmployeeName,
	   e.empnum,  
       e.Email,
	   e.zip
FROM EmployeeTable e  
LEFT JOIN Directors h on e.empnum = h.directorId  
                   AND h.directorid = '9884476'
WHERE e.Password = 'T400s'

Changing to Director's department, from e.Department to h.DeptName did the trick.