I have 3 tables. Table1(Members): 1

[royyan]

I have 3 tables.
Table1(Members): MemberID, MemberName, GroupID
Table2(Log):LogID, MemberID, PeriodID
Table3(Periods): PeriodID, FromDate, ToDate

I want to select members from table1 which don't have logs in table2 for certain PeriodID(table3)

My code is

SELECT * FROM Members m
LEFT JOIN Log pl
ON m.MEMBER_ID= pl.MEMBER_ID
WHERE m.GroupID='2' AND l.PayrollLogID is null


This returns members don't have logs in Table2 for all PeriodIDs. I just don't know how to pull members don't have logs for certain PeriodIDs. Any help will be highly appreciated. Thanks!

 

[PauloRico]

SELECT * FROM Members WHERE MemberID NOT IN
(SELECT MemeberID FROM Log WHERE PeriodID NOT IN
(SELECT PeriodID FROM Periods WHERE FromDate='01/01/2003'))

Treat the above as pseudo code. I have not tested it, but the logic seems to be O.K. (the use of NOT IN with a nested SELECT)

Hope this helps

Paul

 

[royyan]

Thanks Paul! it worked in my case. Simple and straight forward.

 

[ashark]

Hi Group,
I think the Query which you need is

SELECT * FROM Members WHERE MemberID NOT IN
(SELECT MemeberID FROM Log WHERE PeriodID IN
(SELECT PeriodID FROM Periods WHERE FromDate='01/01/2003'))

You should not have a NOT in the second Select Query

-Asha