Problem with IN

[JCV]

Hi,

I need detect if some element in a vector X exists in the vector Y (X and Y are variables)

With the clause IN i solve the one to more, but i dont´t solve the more to one.

The query is some this: Select * from xxxx where (1,3,5) IN (1,3,8,9)

I don´t like write this: Select * from xxxx where ( 1 IN (1,3,8,9) or 3 IN (1,3,8,9) or 5 IN (1,3,8,9) )

Thanks.

 

[bborissov]

Can you provide a simple data and what you want. With the example you gave us you will select ALL records from that xxxx, and I suppose you don't want this.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[JCV]

Hi, Borislav.

For this example supouse what i obtain the values of a user's groups with a query (returning one or more groups)
example : select @groups = '1,3,5'

Then, when make a query of my menu table i need include in the where clause a condition for the menu items
what i have give access.
example : Select * from menu where @groups in menu.groups

Thanks.

 

[bborissov]

You reversed the code, you should ask if the field is contained in the variable, not the opposite, and because I suspect the groups are integer you must use so called dynamic SQL:

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color]  #test ([COLOR=blue]Groups[/color] [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #Test [COLOR=blue]VALUES[/color] (1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #Test [COLOR=blue]VALUES[/color] (2)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #Test [COLOR=blue]VALUES[/color] (3)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #Test [COLOR=blue]VALUES[/color] (4)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #Test [COLOR=blue]VALUES[/color] (5)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #Test [COLOR=blue]VALUES[/color] (6)

[COLOR=blue]DECLARE[/color] @groups [COLOR=blue]varchar[/color](200)
[COLOR=blue]DECLARE[/color] @lcSQL [COLOR=blue]varchar[/color](200)
[COLOR=blue]SET[/color] @groups = [COLOR=red]'1,3,5'[/color]
[COLOR=blue]SET[/color] @lcSQL = [COLOR=red]'SELECT * FROM #Test WHERE Groups IN ('[/color]+@groups+[COLOR=red]')'[/color]
[COLOR=blue]EXEC[/color](@lcSQL)
[COLOR=blue]DROP[/color] [COLOR=blue]Table[/color] #test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

You have a tables with columns that have more than one value in them such as "1,3,5"? You have larger problems (lack of basic normalization) than how to perform this one query.

Split out the groups out into rows, see the FAQs in this forum (how to pass a list of values to a stored procedure) for help on this.

Your current design (problematic):
[tt]Menu
MenuID Name Groups
1 Blah 2,5,7,12[/tt]

Normalized design (best practice):
[tt]Menu
MenuID Name
1 Blah

MenuGroups
MenuID Group
1 2
1 5
1 7
1 12[/tt]

Then you can do something like

IF @Groups LIKE '%[^,0-9]%' BEGIN
   RAISERROR('Invalid characters in @Groups input', 16, 1)
   RETURN
END

SELECT
   MenuID, Name, OtherColumns
FROM
   Menu M
   INNER JOIN (
      SELECT MenuID
      FROM MenuGroups G
      INNER JOIN dbo.SplitInt(@Groups, ',') S ON S.Value = G.Group
   ) X ON M.MenuID = X.MenuID

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

er, toss a DISTINCT in the derived table between SELECT and MenuID...

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]