using keyword in - 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)

using keyword in

Author

Topic

wtech
Starting Member

11 Posts

Posted - 2004-09-16 : 08:28:38

select *
from emp
where deptid in (select top 1 deptid from dept where empid=100)

consider that a employee has more than 1 dept.when the subquery is executed it return a value like 2,3,4.now when i use this value in the master query like:select * from emp where deptid in (2,3,4)
i get the required results

but the problem is when i run the whole query it is executing something like this:
select * from emp where deptid in '2,3,4'.it is considering the value returned from the subquery as a single value.i need to split them in the front end and execute separately again.
can anyone put in a solution?

regards,
Wtech

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-09-16 : 10:08:06

Can you please post the query you are trying! the subquery specified in the top should return only one row.

- Sekar

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-16 : 11:02:05

[code]select emp.*
from emp join dept on emp.deptid = dept.deptid
where emp.empid = 100 -- ???[/code]

quote:
consider that a employee has more than 1 dept

Then there should be a link table between emp table and dept table, I can't see that in your query.
Tell us more about the table design and the query you are trying to do

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

wtech
Starting Member

11 Posts

Posted - 2004-09-17 : 00:55:24

ok.i will give u the details. i have two tables take emp and dept.in emp table there are 3 fields empid(numeric),name(char),deptid(varchar).An employee can belong to more than one department.

next the dept table contains deptid(int) and deptname(varchar).The table design is like this only if i change the datatype of deptid in emp to int then it will result in loss of data so try it this way only.

now for the actual problem

i have written a query which should return the values in dept when an inner slect stament return values from emp table.
insert some values in emp table,take first row as 1 wtech 1,2,3
and these values in dept table
1 finance
2 healthcare
3 corporate sector
the employee wtech belong to more than one dept

now execute this query:it returns zero rows
select * from dept_jk where convert(varchar(50),deptid) in (select top 1 dept_id from emp_jk where emp_id=1)

where it should return all the 3 rows in dept table.

now if i execute the inner query alone i get 1,2,3
if i replace this with the inner query then it runs well.

select * from dept_jk where convert(varchar(50),deptid) in (1,2,3) i get the desired result.

i think the inner query is taking the entire result as a single string
select * from dept_jk where convert(varchar(50),deptid) in ('1,2,3')

hope i got myself through this time.

thanks and regards,
Wtech

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-17 : 03:16:40

Hi,
Your database design is fundamentally wrong, there is a many to many relationship between employees and departments.
iow, an employee can belong to many dept and one dept can have many employess.
This M:M relationship has to be implemented in another table in the database.

Now you are implementing this M:M relationship it in a deptid(varchar) column,
this will become a nightmare to query, and maintain... you already have trouble

Also you loose all referential integrity, in the deptid(varchar) column any nonsense can be put.

Here is how You have to implement this:

create table emp(empid int not null primary key, name varchar(35) not null unique )
create table dept(deptid int not null primary key, name varchar(35) not null unique )
create table empdept(empid int not null references emp(empid), deptid int not null references dept(deptid), primary key(empid,deptid))

insert dept(deptid,name) values(1,'finance')
insert dept(deptid,name) values(2,'healthcare')
insert dept(deptid,name) values(3,'corporate sector')
insert emp(empid,name) values(1,'wtech')
insert empdept(empid,deptid) values(1,1)
insert empdept(empid,deptid) values(1,2)
insert empdept(empid,deptid) values(1,3)

select
	dept.deptid, dept.name
from
	empdept
	join dept on empdept.deptid = dept.deptid
	-- join emp on empdept.empid = emp.empid
where
	empdept.empid = 1

drop table empdept
drop table dept
drop table emp

PS. Put these tables in a database diagram (EM) and you can see how it all is related.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources