0

I have looked at similar posts and have tried them but dint seem to work. In the below query on a MSSQL 2015 database, I need to filter out inactive users. That is, only return results where 'Active' = '1'. I tried:

ON Users.[Name] = Skills.[Name] where Users.[Active] = 1 ---- Incorrect syntax near the keyword 'LEFT'.

ON Users.[Name] = Skills.[Name] where Users.[Active] = '1' ---- Incorrect syntax near the keyword 'LEFT'.

ON Users.[Name] = Skills.[Name] AND Users.[Active] = '1' ---- Query gets executed but shows all results.

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
    ON Users.[Name] = Skills.[Name]
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
    ON Users.[Name] = WeekTimeslip.[employeename] 
LEFT JOIN [master].[dbo].[Project] AS Project
    ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
    ON Users.[Name] = Utilisation.[employeename]
ORDER by Users.[Name]

Can you please let me know what I'm missing?

3
  • I'm not sure I understand. If you just want to filter for active users, that would normally go in your WHERE clause, after the FROM clause and before ORDER BY. Mar 30 '20 at 14:47
  • @JonofAllTrades - thank you, that works! I wasn't sure where the 'WHERE' clause would go.
    – skalazer
    Mar 30 '20 at 15:45
  • @ypercubeᵀᴹ - thank you, that works! I wasn't sure where the 'WHERE' clause would go.
    – skalazer
    Mar 30 '20 at 15:46
0

You can put the filter in your join like this:

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
    ON Users.[Name] = Skills.[Name] AND Users.[Active] = '1'
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
    ON Users.[Name] = WeekTimeslip.[employeename] AND Users.[Active] = '1'
LEFT JOIN [master].[dbo].[Project] AS Project
    ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
    ON Users.[Name] = Utilisation.[employeename] AND Users.[Active] = '1'
ORDER by Users.[Name]

Or just put it in a where clause like this:

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
    ON Users.[Name] = Skills.[Name]
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
    ON Users.[Name] = WeekTimeslip.[employeename] 
LEFT JOIN [master].[dbo].[Project] AS Project
    ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
    ON Users.[Name] = Utilisation.[employeename]
WHERE Users.[Active] = '1'
ORDER by Users.[Name]

Usually, SQL will generate the same execution plan and the optimization process will do pretty much the same thing.

3
  • The first suggestion doesn't seem to work - not sure why. However, the 2nd suggestion with the 'WHERE' clause before the 'ORDER BY' works. Thank you!
    – skalazer
    Mar 30 '20 at 15:52
  • you will probably need to add the same "And" for each join on the user table... I'll update my answer Mar 30 '20 at 15:55
  • Adding 'AND' clause for each join still dint seem to work. The 2nd option still works.
    – skalazer
    Mar 30 '20 at 16:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.