cant figure out how to write this query!!!

dear all...anyone!!!

i have 3 tables

one containing occupations
second containing employees(cpr_number pk, occupation_id fk)
third containing school names(school_id pk, cpr_number fk)

i want to display each school with all the employees in it and the whole list of the occupation from the occupation table if the occupation was vacant then the employee cpr field displayed should be null to notify that this occupation has no employee in it for future hiring ...

i hope i was clear ....

please i need ur help ASAP

I've tried to duplicate what I *think* you mean...

USE [TESTING]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[zOccupation]
(
[id] [int] IDENTITY(1, 1)
NOT NULL
,[OccupationName] [nvarchar](50) NOT NULL
)
ON [PRIMARY]
GO
-------
CREATE TABLE [dbo].[zEmployee](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[OccupationID] [int] NOT NULL
) ON [PRIMARY]

GO
-------
CREATE TABLE [dbo].[zSchool](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[EmployeeID] [int] NOT NULL
) ON [PRIMARY]

GO
-------
Data follows:

ZOCCUPATION
id OccupationName
1 Doctor
2 Lawyer
3 Astronaut
4 Fireman
5 Teacher
6 Dog Catcher

ZEMPLOYEE
id Name OccupationID
1 Abby 1
2 Beth 2
3 Charlie 3
4 David 4
5 Evelyn 5

ZSCHOOL
ID Name EmployeeID
1 AAA 1
2 AAA 2
3 AAA 5
4 BBB 3
5 BBB 4


Query:
SELECT
S.Name
,E.Name
,O.OccupationName
FROM
dbo.zSchool S
JOIN
dbo.zEmployee E
ON S.EmployeeID = E.ID
RIGHT JOIN dbo.zOccupation O
ON E.id = O.id
ORDER BY
S.Name

RESULTS:
Sch. Emp.
Name Name Occupation Name

NULL NULL Dog Catcher
AAA Evelyn Teacher
AAA Abby Doctor
AAA Beth Lawyer
BBB Charlie Astronaut
BBB David Fireman

Hope this is correct!