Problem with a query

I have 3 tables Workers, Event and Worker_Event_Persmissions.The workers table contains a list of workers, the events table contains a list of events – exam, assessments and tests, and finally the worker_event_permissions contains details about the permissions for each worker whether they can view, amend or check.

I want to create a select query that shows all events for each worker and the permissions assigned.I would like the role and event to be displayed even if there are no workerevent permissions setup.

Can anyone help?

Workers

RoleDescription

TeaTeacher

TutTutor

Event

TypeDescription

ExExam

AsmAssessment

TeTest

Worker_Event_Permissions

Role_TypeEvent_TypeViewAmendCheck

TeaExYNY

TeaAsmNNY

TutTeYYY

Query

TypeRoleViewAmendCheck

ExamTeacherYNY

AssessmentTeacherNNY

TestTeacherNULLNULLNULL

ExamTutorNULLNULLNULL

AssessmentTutorNULLNULLNULL

TestTutorYYY

here You go..

Code Snippet

Create Table #workers (

[Role] Varchar(100) ,

[Description] Varchar(100)

);

Insert Into #workers Values('Tea','Teacher');

Insert Into #workers Values('Tut','Tutor');

Create Table #event (

[Type] Varchar(100) ,

[Description] Varchar(100)

);

Insert Into #event Values('Ex','Exam');

Insert Into #event Values('Asm','Assessment');

Insert Into #event Values('Te','Test');

Create Table #worker_event_permissions (

[Role_Type] Varchar(100) ,

[Event_Type] Varchar(100) ,

[View] Varchar(100) ,

[Amend] Varchar(100) ,

[Check] Varchar(100)

);

Insert Into #worker_event_permissions Values('Tea','Ex','Y','N','Y');

Insert Into #worker_event_permissions Values('Tea','Asm','N','N','Y');

Insert Into #worker_event_permissions Values('Tut','Te','Y','Y','Y');

Query

Select WD,WD,[View],[Amend],[Check] From

(

Select

W.Role WR, E.Type ET,

W.[Description] WD,E.[Description]ED

from

#workers W Cross Join #event E

) as Data

Left Outer Join

#worker_event_permissions WEP

On WEP.Role_Type = Data.WR And WEP.Event_Type=Data.ET