Left join problem

• mageshh11

  Old Hand

  Points: 377

  More actions

  July 5, 2011 at 2:52 am

  #99741

  Hi,

  I need a help on the below:

  Have two tables. One is master and next is details.

  I need a result which satisfies below conditions

  All the rows from master even if rows are not in details.

  If rows is present then Date column should be NULL or Greater than Current Date.

  I have tried using left join, but not getting the desired result.

  Please help. Thanks in advance.

  sample structure is as below:

  Master:

  ID Name

  1DWK

  2 MWPL

  3RK

  Details:

  * n - may be any integer

  IDName DateMName

  1STM1 NULLDWK

  2STM2 Current Date-nDWK

  3STM3 CurrentDate+nDWK

  4STM4 NULLRK

  5STM5 Current Date-nRK

  Now my result should be as below:

  IDName Date

  1STM1 NULL

  3STM3 CurrentDate+n

  4STM4 CurrentDate+n

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  July 5, 2011 at 3:34 am

  #1348134

  If I understand your question correctly, this could be an answer:

  -- create temp tables

  DECLARE @master TABLE (

  ID int NOT NULL PRIMARY KEY,

  Name varchar(4)

  )

  DECLARE @Details TABLE (

  ID int NOT NULL,

  Name char(4),

  [Date] datetime,

  MName varchar(4)

  )

  -- insert sample data

  INSERT INTO @master

  SELECT 1, 'DWK'

  UNION ALL SELECT 2, 'MWPL'

  UNION ALL SELECT 3, 'RK'

  INSERT INTO @Details

  SELECT 1, 'STM1', NULL, 'DWK'

  UNION ALL SELECT 2, 'STM2', CONVERT(char(8), DATEADD(day, -3, GETDATE()), 112), 'DWK'

  UNION ALL SELECT 3, 'STM3', CONVERT(char(8), DATEADD(day, +3, GETDATE()), 112), 'DWK'

  UNION ALL SELECT 4, 'STM4', NULL, 'RK'

  UNION ALL SELECT 5, 'STM5', CONVERT(char(8), DATEADD(day, -3, GETDATE()), 112), 'RK'

  -- select out results

  SELECT D.ID, D.Name, D.[Date]

  FROM @master AS M

  INNER JOIN @Details AS D

  ON M.Name = D.MName

  WHERE D.[Date] IS NULL

  OR D.[Date] >= CONVERT(char(8), GETDATE(), 112)

  Based on your sample data, the expected results for the third row (ID = 4) should not include a date, but the NULL value.

  Also, since you are not selecting fields from the master table, an INNER JOIN is enough.

  Did I miss something?

  -- Gianluca Sartori

• mageshh11

  Old Hand

  Points: 377

  More actions

  July 5, 2011 at 3:43 am

  #1348146

  Thanks for your quick response. But

  Sorry it is actuall my mistake. I given the sample output wrongly.

  The output should be: (All the rows from master even if rows are not in details.)

  (If rows is present then Date column should be NULL or Greater than Current Date.)

  M.IDM.NameD.Name D.Date

  1DWKSTM1NULL

  1DWKSTM3CurrentDate+n

  2MWPLNULLNULL

  3RKSTM4NULL

  Please help.

• mageshh11

  Old Hand

  Points: 377

  More actions

  July 5, 2011 at 3:45 am

  #1348148

  In this case inner join will not give all the rows from master table.

  We should use left join only. But if left join is used, date clause in not giving the exact result.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  July 5, 2011 at 3:56 am

  #1348150

  mageshh11 (7/5/2011)

  ---

  Thanks for your quick response. But

  Sorry it is actuall my mistake. I given the sample output wrongly.

  The output should be: (All the rows from master even if rows are not in details.)

  (If rows is present then Date column should be NULL or Greater than Current Date.)

  M.IDM.NameD.Name D.Date

  1DWKSTM1NULL

  1DWKSTM3CurrentDate+n

  2MWPLNULLNULL

  3RKSTM4NULL

  Please help.

  With these expected results, the code can be changed this way:

  SELECT M.ID, M.Name, D.[Date]

  FROM @master AS M

  LEFT JOIN @Details AS D

  ON M.Name = D.MName

  WHERE D.[Date] IS NULL

  OR D.[Date] >= CONVERT(char(8), GETDATE(), 112)

  -- Gianluca Sartori

• mageshh11

  Old Hand

  Points: 377

  More actions

  July 5, 2011 at 7:29 am

  #1348266

  Thanks for the quick response.

  That solved my issue.