Multiple SQL select statements

[jennypretty]

Hello,

I am using sql server database. I have this select statement and would like to ask one question about this:

tbl_student
ID Dept Name
-----------------
1 A jenny
2 B beta
3 C jim
4 D kim
5 B beta
6 C jim

qry_student
select distinct Name from tbl_student where Name in ('jenny','beta','jim')

Output:
Name
-----
jenny
beta
jim

On ASP page, I use this statement:
select Name from student where Name='jenny' OR Name='beta' OR Name='jim'

It works.

When I do dynamically by using this statement:
select Name from student where Name IN ('select Dept from qry_student')

It does not work. I am trying to return the value of the Name in either (jenny, beta, or jim) but it does not return the value.

How do I change this statement to make it work dynamically?
select Name from student where Name IN ('select Dept from qry_student')

Thanks.

[siteguru]

So the Dept field in table qry_student has values of jenny, jim etc?

What exactly are you trying to achieve? Don't explain in terms of SQL statement - explain in terms of how the tables are constructed and what output you desire.

[r937]

When I do dynamically by using this statement:
select Name from student where Name IN ('select Dept from qry_student')

It does not work.

well, no, of course not

the contents inside the parentheses is a string

this is valid syntax, however, it is equivalent to asking for the student where the student name is 'select Dept from qry_student'

no student is gonna have that name, right?

also, suppose you did remove the quotes, so that it isn't a string but a proper subquery

now you'd be looking for a student with the same name as a department

i don't think you're going to find any results from that, either

[jennypretty]

qry_student is a view that lists only ('jenny','beta','jim')

Because I need to re-use this criteria many times in the script so I want to do this dinamically.
select Name from student where Name IN ('select Dept from qry_student')

It only lists those records that meet this criteria.

Later on, if I want to add or change the names, then I need to change on table or query only.

However, it lists no records.

Thanks.

[siteguru]

select Name from student where Name IN (select Dept from qry_student)

[jennypretty]

That's it.
I worked.
Thanks very much.

[r937]

thanks, ian

apparently my explanation in post #3 of what was wrong was too subtle

[siteguru]