PLEASE HELP with Query

17 Aug 2006 7:43 PM

Michael Kintner

I am trying to use a IN statement

Select * From ShowMenusFirst WHERE (Security In ((select Security from Users
Where (Username='mkintner')))) ORDER BY Menus.Level;

The results from the query select Security from Users Where
(Username='mkintner') is 'Low','Med'

I wanted the IN Statement to be WHERE Security IN ('Low','Med')

If I type out the SQL Select * From ShowMenusFirst WHERE (Security In
('Low','Med')) it works fine but for some reason adding the select
statement inside of the IN statement nothing happens, any ideas as to why
this is not working?

Mike

17 Aug 2006 7:57 PM

--CELKO--

Michael Kintner wrote:
Show quote

> I am trying to use a IN statement
>
> Select * From ShowMenusFirst WHERE (Security In ((select Security from Users
> Where (Username='mkintner')))) ORDER BY Menus.Level;
>
> The results from the query select Security from Users Where
> (Username='mkintner') is 'Low','Med'
>
> I wanted the IN Statement to be WHERE Security IN ('Low','Med')
>
> If I type out the SQL Select * From ShowMenusFirst WHERE (Security In
> ('Low','Med')) it works fine but for some reason adding the select
> statement inside of the IN statement nothing happens, any ideas as to why
> this is not working?

When you put a SELECT inside parens, it becomes a subquery. If it is
in a list, it is assumed to be a scalar subquery, so you did the query
and tried to convert it to a scalar. What you probably meant to write
is:

SELECT * -- do not use * in production code
FROM ShowMenusFirst
WHERE security
    IN (SELECT security
          FROM Users
         WHERE user_name = 'mkintner')
ORDER BY Menus.level; -- where is this table?

Never SELECT * in production code; it will bite you. Where is this
Menus table you used in the ORDER BY clause? Certainly not in the
query.

17 Aug 2006 8:17 PM

Steve Dassin

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1155844638.694134.148920@i42g2000cwa.googlegroups.com...

> Never SELECT * in production code; it will bite you.

I've always wondered if this was the reason you never put 'drop/remove
column' in sql.
Gotcha:)

http://racster.blogspot.com/

17 Aug 2006 8:15 PM

Alejandro Mesa

Michael,

You will have to split the string or use the function "charindex" instead.
SQL Server is seeing the result as a string and the IN operator will compare
both sides as a whole value.

....
where (security = '''Low'', ''Med''' or ...)

try:

declare @s varchar(50)

set @s = (select Security from Users Where Username='mkintner')

Select *
From ShowMenusFirst
where charindex('''' + security + '''', @s) > 0
go

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html

AMB

Show quote

"Michael Kintner" wrote:

> I am trying to use a IN statement
>
> Select * From ShowMenusFirst WHERE (Security In ((select Security from Users
> Where (Username='mkintner')))) ORDER BY Menus.Level;
>
> The results from the query select Security from Users Where
> (Username='mkintner') is 'Low','Med'
>
> I wanted the IN Statement to be WHERE Security IN ('Low','Med')
>
> If I type out the SQL Select * From ShowMenusFirst WHERE (Security In
> ('Low','Med')) it works fine but for some reason adding the select
> statement inside of the IN statement nothing happens, any ideas as to why
> this is not working?
>
> Mike
>
>
>

18 Aug 2006 2:20 PM

Michael Kintner

Hey, thats great it is getting me closer, so how do I perform this function
in one select statement?

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:6C2A36E9-393A-4CC3-9BC1-D1177B945BF9@microsoft.com...
> Michael,
>
> You will have to split the string or use the function "charindex" instead.
> SQL Server is seeing the result as a string and the IN operator will
> compare
> both sides as a whole value.
>
> ...
> where (security = '''Low'', ''Med''' or ...)
>
> try:
>
> declare @s varchar(50)
>
> set @s = (select Security from Users Where Username='mkintner')
>
>
> Select *
> From ShowMenusFirst
> where charindex('''' + security + '''', @s) > 0
> go
>
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html
>
>
> AMB
>
> "Michael Kintner" wrote:
>
>> I am trying to use a IN statement
>>
>> Select * From ShowMenusFirst WHERE (Security In ((select Security from
>> Users
>> Where (Username='mkintner')))) ORDER BY Menus.Level;
>>
>> The results from the query select Security from Users Where
>> (Username='mkintner') is 'Low','Med'
>>
>> I wanted the IN Statement to be WHERE Security IN ('Low','Med')
>>
>> If I type out the SQL Select * From ShowMenusFirst WHERE (Security In
>> ('Low','Med')) it works fine but for some reason adding the select
>> statement inside of the IN statement nothing happens, any ideas as to why
>> this is not working?
>>
>> Mike
>>
>>
>>

18 Aug 2006 8:37 PM

Tracy McKibben

Michael Kintner wrote:

> Hey, thats great it is getting me closer, so how do I perform this function
> in one select statement?
>

SELECT ShowMenusFirst.*
FROM ShowMenusFirst
INNER JOIN Users
ON CHARINDEX('''' + ShowMenusFirst.Security + '''', Users.Security) > 0
WHERE Users.Username = 'mkintner'

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com