SQL select - 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL select - 2 tables

Author

Topic

nkpriya
Starting Member

34 Posts

Posted - 2015-04-27 : 13:34:16

Hi
I have 2 tables -
1st table is weekly timesheets table has employee ID, Period Name, etc
2nd table is employee list from HR which has employee ID, manager ID.
I would like to know who did not submit timesheets each week as have period name in the 1st table.
How can I do that?
Thanks for your help

Kristen
Test

22859 Posts

Posted - 2015-04-27 : 13:39:42

You haven't provided the actual names of the tables and only given vague column names, so you'll have to work that out for yourself from this example:


SELECT *
FROM Table_2 AS EMP
WHERE NOT EXISTS
(
    SELECT *
    FROM Table_1 AS TS
    WHERE     TS.EmployeeID = EMP.EmployeeID
          AND TS.PeriodName = 'XXX'
)

nkpriya
Starting Member

34 Posts

Posted - 2015-04-27 : 13:52:44

Thank you.
Instead of giving period name, can we do group by Period Name? so that it displays employees missing for each period.Thanks!

nkpriya
Starting Member

34 Posts

Posted - 2015-04-27 : 18:54:59

can you please respond.

SELECT *
FROM Table_2 AS EMP
WHERE NOT EXISTS
(
SELECT *
FROM Table_1 AS TS
WHERE TS.EmployeeID = EMP.EmployeeID
AND TS.PeriodName = 'XXX'
)

This is working for just one period name, I would like period name in select statement and for all periods whatever available. Please help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources