February 28, 2007 at 2:21 pm
Here is a nasty one. I need to return repondentsid that match between a lookup table
'we will call it tblLookup' and the primary event table 'we'll call it EventLog': The EventLog table contains eventids for a given respondentid. Lets say respondentid 222 had 5 different eventcode (1,2,3,4,5). But what needs to happen is out of the lookup table I only want the respondentids that do not include eventid 2 or 3. Code so far:
Select Distinct RespondentID from EventLog e
INNER JOIN tblLookup l ON e.RespondentID = l.RespondentID
This is the primary join which joins the Lookup tabl eto the eventlog table. The issue is when I try to add the code
Select e.RespondentID from EventLog e
INNER JOIN tblLookup l ON e.RespondentID = l.RespondentID
WHERE e.eventID Not In (2,3)
It's not excluding the respondentid that contain eventId 2,3, Its just not returning those two event s for that respondentid. Please Help. Desperately.....
February 28, 2007 at 2:33 pm
Select e.RespondentID
From EventLog e
Inner Join tblLookup l
On e.RespondentID = l.RespondentID
Where Not Exists (
Select *
From EventLog As e2
Where e2.RespondentID = e.RespondentID
And e2.eventID In (2,3)
)
February 28, 2007 at 2:35 pm
PW beat me to it, but here's another flavor.
SELECT e.RespondentID
FROM EventLog e
INNER JOIN tblLookup l
ON e.RespondentID = l.RespondentID
LEFT JOIN (SELECT DISTINCT RespondentID FROM EventLog WHERE EventID NOT IN (2,3)) t1
ON t1.RespondentID = e.RespondentID
WHERE t1.RespondentID IS NULL
February 28, 2007 at 6:35 pm
You guys rock!!! PW's script worked like a champ. Although John's did return any thing. Not sure why. But thanks a lot.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply