How do I display only records belonging to a particular department?

Transact-SQL
1

Greetings again experts,

I have the following tables:

Directors
[DeptNum] nvarchar NULL,
[DeptName] nvarchar NULL,
[EmpName] nvarchar NULL,
[DirectorID] nvarchar NULL,
[EmpTitle] nvarchar NULL

Employees:
[Department] nvarchar NULL,
[DeptNum] nvarchar NULL,
[Unit] nvarchar NULL,
[UnitName] nvarchar NULL,
[EmpName] nvarchar NULL,
[EmpNum] nvarchar NOT NULL

Comp
[CompID] [int] IDENTITY(1,1) NOT NULL,
[EmpNum] nvarchar NULL,
[ManagerID] nvarchar NULL,
[Phone] nvarchar NULL,
[Deptnumber] nvarchar NULL

Employees and Directors tables are look up tables and all records are inserted into Comp table.

My task is to show all the employees who belong to a particular department.

I am also required to show who the director of that department is.

The code so far is not producing the correct results.

Can you please help?

Thanks in advance

                Select distinct d.empname as directorName, a.status,e.empnum,e.empname,e.department,e.unitName
                from Comp c inner join Directors d on d.deptnum = c.deptnumber
                inner join Employees e on c.empnum =  e.empnum 
                where c.status ='Done' and d.deptnum=@deptId
2

You are refering to a.status and a.deptnumber but doesn't seem to have a table/alias named a. You do have a field in comp table named deptnumber, so I'm guessing field status is in the same table (which is not shown in your table description).
In comp table, you have managerid field, which I assume points to directorid field in director table. So I would suggest this query:

select distinct
       d.empname as directorname
      ,c.status
      ,e.empnum
      ,e.empname
      ,e.department
      ,e.unitname
  from comp as c
       inner join directors as d
               on d.directorid=c.managerid
       inner join employees as e
               on e.empnum= c.empnum 
 where c.status='Done'
   and c.deptnumber=@deptid
;

If this doesn't work for you, please post table descriptions as create statements, sample data as insert statements and expected output (from your sample data).

3

Hi bitsmed,

I am sorry, I was trying to remove sensitive data and in the process missed some important items.

I have replaced any reference of a.field with c.field

The only relationship between Directors table and Comp table is deptnumber in Comp table and deptnum in Directors table.

Should I add a field called DirectorId in Comp table and relate it to Directors table based on Directorid to EmpNum.

I was under the impression that Comp.DeptNumber = Directors.EmpNum would work.

Thanks for your help

4

I can't realy tell, unless you show some sample data.
Are there more directors to a single department?
ps. you missed changing the a.status in select section.