-
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|