SELECT IN () Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT IN () Question

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-29 : 03:51:11

Hello everyone,

I have 2 query's
SELECT idUser
FROM tblnaw

Result: 1

SELECT idUser
FROM tblusers

Result: 1,2,3,4

Now If I combine these two like this:

SELECT idUser
FROM tblusers
WHERE idUser
IN (
SELECT idUser
FROM tblnaw
)

I get an error message:
SELECT idUser FROM tblnaw )' at line 1

I dont understand because when I enter:
SELECT idUser
FROM tblusers
WHERE idUser
IN (
1,2
)

It works

Hope you can help me out?

thanxx
Bjorn

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-29 : 04:05:09

That should work....unless you have some non-displayable (control)character embedded in the query line...

A 'WHERE EXISTS' or an INNER JOIN style statement will work.

SELECT idUser FROM tblusers a WHERE exists (SELECT idUser FROM tblnaw b where b.idUser = a.idUser)

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-29 : 04:19:28

Nop,

error:
exists ( SELECT idUser FROM tblnaw )' at line 1

Its the same error, but when I enter:
SELECT idUser
FROM tblusers
WHERE exists (
1,2)

Its not working but I think thats logic.

Cya
Bjorn

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-29 : 04:26:19

I think theres something else wrong.

Both queries syntax are correct (where exists() and iduser in())

Duane.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-29 : 04:26:50

The "Where Exists" subquery needs a link to the outer query....just as i did with my example.

also for me
1a. select * from customer where exists (select 10)
works (in that the code executes, but delivers the wrong (expected) result)
whereas
1b. select * from customer where exists (select * from customer where code = 10)
works (in that the code executes, but delivers the wrong (expected) result)
1c. select * from customer a where exists (select * from customer b where a.code = b.code and b.code = 10)
works (in that the code executes, and delivers the expected result)

but
2. select * from customer where exists (10)
doesn't...execute at all.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-29 : 04:39:36

Agreed, but it does not explain the syntax error because the syntax is fine even though the results won't be what is expected.

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-29 : 05:26:28

Bjorn,
Looking at your latest post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33787

It seems like this code might not be SQL server code but perhaps some other db code maybe thats why there is this confusion.

Are you using SQL Server here?

Duane.

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-29 : 05:32:23

No I use mysql but that would not make any difference is it?
Because when I use:
SELECT idUser
FROM tblusers
WHERE idUser
IN (
1,2
)

it works. Only when I replace the 1,2 with a query that produced the same 1,2 its not working!

Thanxx
Bjorn

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-29 : 05:38:27

I think it might well make a difference.

Look at it this way, the query you posted on the other thread returns syntax errors in SQL Server QA and thats supposd to be working)

Duane.

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-29 : 05:52:16

How can you test this? You dont have my tabel structuur so I thinks this is not possible for you to test? Or is it just a mather of speaking?

Here you can see that the IN operator should work fine in mysql even with a subquery:
http://www.mysql.com/doc/en/ANY_IN_SOME_subqueries.html

cya
Bjorn

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-29 : 06:24:05

Aha........while you use "mysql"...this site primarily (99.9%+) is a MSSQLServer resource....and that small difference is an important one.....that why my MSSQL statement works!

As a total aside....I love the statement within that link

"To most people's ears, the English phrase 'a is not equal to any b' means 'there is no b which is equal to a,' but that isn't what is meant by the SQL syntax"

A <> B always means B <> A......and I fail to see how they can find another meaning for it. Maybe they have invented a new branch of mathematics!

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-29 : 06:54:02

He he that is funny, I did not even read than peace of text.

But still strange why my solution is not working. Can it be something with relations that have to be here else it wont work? I dont think so.

Cya
Bjorn

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-29 : 06:55:11

maybe posting this issue on a "mysql community" forum, will drive an answer faster?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-29 : 08:29:35

Try

SELECT u.idUser
FROM
tblusers u
INNER JOIN tblnaw n ON u.idUser = n.idUser

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-29 : 12:28:41

Or try

http://www.dbforums.com/f5/

Brett

8-)

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-03-29 : 16:03:54

Dolphin_Kabobs == Yumm

It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources