An inclusive report - i.e. including nulls

• simon.wilson 8043

  Old Hand

  Points: 364

  More actions

  November 5, 2009 at 6:16 am

  #216447

  Hi,

  Having just started using Visual Studio 2005, I am stuck on a particular request that has come into our dept.

  What I basically need is a list of all people, but to also include (where appropriate) those who have completed a particular training course.

  Our training dept is trying to keep control of who has and who hasnt done this particular course, so would like the list to include everyone, and then show those who are qualified. They can then plot out who will need to be trained.

  So in essence what I need to come up with is as follows:

  Name Date of Course Result

  A. Brown

  D. Smith 20/10/2009 Pass

  E. Jones

  S. Williams 18/09/2009 Fail

  J. Daniels 14/06/2009 Pass

  I have tried to do it using two tables - the 'personnel' table, and the 'training courses taken' table, and even when I use an inner join on the personnel table (to try and include all staff) it doesnt seem to work! 🙁 (the 'training courses taken' table allows me to pick up those who have taken a course but failed to pass)

  I am thinking that i need to possibly nest a statement somewhere, but am not sure where to put the nesting (i.e. in the 'select' part, or the 'where' part of the statement).

  Any help in determining the structure of the query would be greatefully received! 🙂

• only4mithunc

  SSCrazy Eights

  Points: 8434

  More actions

  November 5, 2009 at 6:47 am

  #1075303

  Hi if I understood your problem correctly, it can be done by LEFT JOIN as bellow. Is my understanding is wrong please let me know more details.

  --CREATE TABLE PERSONEL

  --(

  --NAME VARCHAR(30)

  --)

  --GO

  --CREATE TABLE TRAINING

  --(

  --NAME VARCHAR(30),

  --DATE DATETIME,

  --RESULT VARCHAR(30)

  --)

  --GO

  --INSERT INTO PERSONEL

  --SELECT 'A'

  --UNION ALL

  --SELECT 'B'

  --UNION ALL

  --SELECT 'C'

  --UNION ALL

  --SELECT 'D'

  --INSERT INTO TRAINING

  --SELECT 'A',GETDATE(),'PASSED'

  --UNION ALL

  --SELECT 'C',DATEADD(D,-1,GETDATE()),'FAILED'

  Here I have created two tables as you secified , and below is the query.

  SELECT PERSONEL.NAME,ISNULL(CONVERT(VARCHAR(30),TRAINING.DATE,103),'')AS DATE,ISNULL(CONVERT(VARCHAR(30),TRAINING.RESULT,103),'') AS RESULT

  FROM PERSONEL

  LEFT JOIN TRAINING

  ON PERSONEL.NAME = TRAINING.NAME

  Regards,

  MC

  Thanks & Regards,
  MC