Select where records dont exist

• daniel.hobbs-939069

  SSC Rookie

  Points: 31

  More actions

  September 2, 2011 at 4:57 am

  #256468

  I have a table of employess with 1049 records, and a table of training assessment results, and a table of training modules with 209 records.

  What im trying to do is for each employee is see if they have a training result for each module, if there is then bring back their result and if there isnt just bring back null as the result. So in theory i should have 219241 records back.

  How can i do this? Im getting slightly confused by the joins as its only bringing back records where there is a training record.

  tblTRAINASSESS has columns: EMPLOYEE_ID, TRAINRESULT, TRAINMODULE_ID, RESULTDATE

  tblTRAINMODULE has columns: ID, MODULE_NAME

  tblEMPLOYEE has columns: ID, FIRSTNAME, SURNAME

  Hopefully somebody can shed some light for me on this. Thanks

• Latheesh NK

  SSCertifiable

  Points: 6559

  More actions

  September 2, 2011 at 5:39 am

  #1376370

  Is this the below you looking for:

  create Table tblEMPLOYEE

  (ID int, FIRSTNAME Varchar(50))

  Insert into tblEMPLOYEE Values( 1,'John')

  Insert into tblEMPLOYEE Values( 2,'Lee')

  create Table tblTRAINMODULE

  (ID int, MODULE_NAME Varchar(50))

  Insert into tblTRAINMODULE Values( 100,'Design')

  Insert into tblTRAINMODULE Values( 200,'Testing')

  Create Table tblTRAINASSESS

  (EMPLOYEE_ID int , TRAINRESULT Bit, TRAINMODULE_ID int)

  Insert into tblTRAINASSESS Values( 1,1,100)

  Insert into tblTRAINASSESS Values( 2,1,200)

  Select * From

  (

  Select A.ID As EID,C.ID As MID From tblEMPLOYEE A

  Cross Join tblTRAINMODULE C )A

  Left Join tblTRAINASSESS B On A.EID = B.EMPLOYEE_ID and A.MID = B.TRAINMODULE_ID

• Jeff Moden

  SSC Guru

  Points: 1004311

  More actions

  September 2, 2011 at 7:55 pm

  #1376731

  sqlzealot-81 does the trick although you could get rid of the outer select.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)