filter within procedure
-
Here is a part of procedure:
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(600)
SET @sql =
'Select distinct
Events.num1,
Events.num2,
Events.num3
From Events
Inner Join EventMembers On EventMembers.num1 = Events.num1
Where EventMembers.UserID IN (' + @variable + ')'
EXEC(@SQL)
END
That procedure chooses specific events for a user. Events table is also associated with another table called FOO in the database on num1. Table FOO has a couple fields for each num1 that have some responses in them. At this point by this procedure on top user gets all events that are associated with him. How can I change it so that it also looks for each num1 if it even has any fields in it in table FOO. If it doesn't then just don't SELECT it, so user doesn't get an Event(num1) that has no fields with responses for it anyway.
Hoping someone can help, cuz I'm very confused.
-
If FOO has ROWS all the time and you fill fiedls values for responses
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(600)
SET @sql =
'Select distinct
Events.num1,
Events.num2,
Events.num3
From Events
Inner Join EventMembers On EventMembers.num1 = Events.num1 INNER JOIN FOO.num1 on Events.num1=FOO.num1
Where EventMembers.UserID IN (' + @variable + ')
and
FOO.Field1<>'''' and FOO.Field2<>'''''
EXEC(@SQL)
END
If FOO contains rows only when you have an answer than cut this part
and
FOO.Field1<>'''' and FOO.Field2<>''''' from Query
Kindest Regards,Vasc
-
INNER JOIN FOO.num1
or left join?
-
if you put left you will get all rows even if they don't have rows in FOO
Kindest Regards,Vasc
-
Oh ok,
Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply