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)

IN QUESTION

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-05-26 : 11:06:19

Hello Everyone,

why cant I use this:

select *
from tblusers as b
where b.idUser IN (select a.idUser from tblnaw as a)
AND b.idUser IN (select c.idUser from tblijsbrekeres as c)

Result:
select a.idUser from tblnaw as a
is 1, 2, 3

Result:
select * from tblusers as b
is 1, 2, 3, 4, 5, 6

So the result I want is 1, 2, 3

thanxx
Bjorn

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-26 : 11:41:08

whats the count from tblijsbrekeres???

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-05-26 : 11:47:59

Sorry mist one tabel.

tblijsbrekers is 1, 2, 3, 6, 7, 8

Thanxx
Bjorn

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-26 : 11:56:22

So what exactly is the problem???

Seems fine to me..


create table #tblnaw (
idUser int
)
create table #tblusers
(
idUser int
)
create table #tblijsbrekeres
(
idUser int
)
insert into #tblnaw
select 1 union select 2 union select 3

insert into #tblusers
select 1 union select 2 union select 3 union select 4 union select 5 union select 6

insert into #tblijsbrekeres 
select 1 union select 2 union select 3 union select 6 union select 7 union select 8

select * 
from #tblusers as b
where b.idUser IN (select a.idUser from #tblnaw as a)
AND b.idUser IN (select c.idUser from #tblijsbrekeres as c)

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-05-26 : 12:31:07

#
# Table structure for table 'ijsbrekers'
#

CREATE TABLE IF NOT EXISTS ijsbrekers (
userId smallint(3) unsigned NOT NULL auto_increment,
PRIMARY KEY (userId)
);

#
# Dumping data for table 'ijsbrekers'
#
INSERT INTO ijsbrekers VALUES("3");
INSERT INTO ijsbrekers VALUES("4");
INSERT INTO ijsbrekers VALUES("5");
INSERT INTO ijsbrekers VALUES("6");

#
# Table structure for table 'naw'
#

CREATE TABLE IF NOT EXISTS naw (
userId smallint(3) unsigned NOT NULL auto_increment,
PRIMARY KEY (userId)
);

#
# Dumping data for table 'naw'
#
INSERT INTO naw VALUES("1");
INSERT INTO naw VALUES("2");
INSERT INTO naw VALUES("3");

#
# Table structure for table 'users'
#

CREATE TABLE IF NOT EXISTS users (
userId smallint(3) unsigned NOT NULL auto_increment,
PRIMARY KEY (userId)
);

#
# Dumping data for table 'users'
#
INSERT INTO users VALUES("1");
INSERT INTO users VALUES("2");
INSERT INTO users VALUES("3");
INSERT INTO users VALUES("4");
INSERT INTO users VALUES("5");
INSERT INTO users VALUES("6");
INSERT INTO users VALUES("7");
INSERT INTO users VALUES("8");
INSERT INTO users VALUES("9");
INSERT INTO users VALUES("10");

select *
from users as b
where b.userId IN (select a.userId from naw as a)
AND b.userId IN (select c.userId from ijsbrekers as c)

This does not work. But all single query's work!
Hope you can help me out.

Thanxx
Bjorn

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-26 : 13:15:27

What database are you using btw as this isn't SQL Server DDL?!?!?

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-26 : 13:17:44

What database is this? mySQL?

I always thought of this kind of like my SQLTeam...

Never mySQLTeam though...

Is it mySQL?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-26 : 13:25:59

I get 3...which is what you're suppose to get


USE Northwind
GO

CREATE TABLE ijsbrekers (userId int PRIMARY KEY)
GO

INSERT INTO ijsbrekers(UserID)
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
GO

CREATE TABLE naw (userId int PRIMARY KEY)
GO

INSERT INTO naw(UserID)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
GO

CREATE TABLE myUsers99 (userId int PRIMARY KEY)
GO

INSERT INTO myUsers99(UserID)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10
GO

SELECT * 
  FROM myUsers99
 WHERE userId IN (SELECT userId FROM naw)
   AND userId IN (SELECT userId FROM ijsbrekers)
GO

DROP TABLE ijsbrekers
DROP TABLE naw
DROP TABLE myUsers99
GO

Brett

8-)

EDIT: Maybe you could check out http://www.mySQLTeam.com

?

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-05-26 : 14:45:11

Yes it is MySQL, not everybody can offord(earn) a SQLserver.
I wish there was a http://www.mySQLTeam.com

So nobody can help me with this one? Thats a shame.

Thanx anyway
Bjorn :-(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources