Results 1 to 2 of 2

Thread: cant figure out how to write this query!!!

  1. #1
    Join Date
    Jun 2010
    Posts
    1

    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

  2. #2
    Join Date
    Jun 2010
    Posts
    1
    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
  •