3 Table JOIN. Please help me with this query

[MsLady]

I have a three tables:
Employee table (empId, firstname, lastname)
Positions table (positionId, title)--{lists positions}
EmpPositions table (empId, empPositionId, positionId)

Not all employee have positions (i.e. not all employees have records in the empPositions table).
Some employee's position have been archived (archived=1).

I'd like run a query that -->select firstname, lastname, title
1. If an employee does not have a record in the empPositions table, i'd like the title to read "-no active position").
2. If an employee's position have been archived (archived =1), i'd also like the title to read "-no active position").
3. If the employee's has a position, then display the title.

-->select firstname, lastname, title

I have written the query but something is missing and i'd like to start over, as the people with archived position don't show up.

So, how would you write this?
I am using sql server 2000 (t-sql. stored procedure)

 

[MsLady]

Nobody?

 

[dkinley]

Use left-joins for your tables so the records without entries (nulls) will be displayed.

For the fields (columns) of data use an expression. For example ...

Employee Position: IIf (IsNull([EmployeePosition],"-no active position", [EmployeePosition])

-dK

 

[MsLady]

Hi dkinley, thanks for that.
I have decided to use the "select case". It's gotten me close enough, except one thing, perhaps you or anyone can offer more help?

Here's my query now:

SELECT dbo.Employees.empId, dbo.Employees.FirstName, dbo.Employees.LastName, dbo.EmpPositions.empPositionId,
dbo.EmpPositions.positionId, dbo.EmpPositions.ArchiveFlag, dbo.Positions.Title, dbo.Employees.OfficeId,
case(dbo.EmpPositions.ArchiveFlag)
when 0 then dbo.Positions.Title
when 1 then '-no active position'
when null then '-no active position'
else 'no'
End AS TitleName
FROM dbo.Employees LEFT OUTER JOIN
dbo.Positions INNER JOIN
dbo.EmpPositions ON dbo.Positions.positionId = dbo.EmpPositions.positionId ON dbo.Employees.empId = dbo.EmpPositions.empId
where dbo.Employees.firstname like '%sally%'
ORDER BY dbo.Employees.empId

Now There's an employee by the name "Sally Thompson" that has 2 archived positions. i.e. two positions in the empPositions table.
Sally shows up twice in my results and she gets displayed twice (two exact records).
Is there a way i can tell the above query to only display one when an employee shows up more than once? With thier empPosition (title) simply reading as "-no active position".

Please heeelp?!!!

 

[dkinley]

Off the top of my head I would spout out something like there is a setting called 'Unique Values' or records or something.

I am not a query guru so couldn't specify without any details but it might be place to start?

-dK

 

[MsLady]

Okay thank you much!! i'll look into that.

Im still searching folks....please heeeeeeeeeeeeeeeelp

 

[GregVB]

where does "Sally" show up twice? Which of her 2 entries do you want to take?

 

[MsLady]

where does "Sally" show up twice? Which of her 2 entries do you want to take?

Thanks for ur time GregVB,
Sally shows up twice cos she's had 2 positions with the company (2 records in the empPositions table). She is no longer with the company, so the 2 positions she held have been archived (i.e. field Archiveflag=1).

I'd like to select the one with the most recent EffectiveDate in the EmpPositions' table IF the record shows up more than once for a person. I just don't know how

Any help will be greatly appreciated

 

[MsLady]

Here's the query i have now.

SELECT dbo.Employees.empId, dbo.Employees.FirstName, dbo.Employees.LastName, dbo.EmpPositions.empPositionId,
dbo.EmpPositions.positionId, dbo.EmpPositions.ArchiveFlag, dbo.Positions.Title, dbo.Employees.OfficeId,
case(dbo.EmpPositions.ArchiveFlag)
when 0 then dbo.Positions.Title
when 1 then '-no active position'
when null then '-no active position'
else 'no'
End AS TitleName
FROM dbo.Employees LEFT OUTER JOIN
dbo.Positions INNER JOIN
dbo.EmpPositions ON dbo.Positions.positionId = dbo.EmpPositions.positionId ON dbo.Employees.empId = dbo.EmpPositions.empId
where dbo.Employees.firstname like '%sally%'
ORDER BY dbo.Employees.empId