SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Non-Member
    Join Date
    Jul 2008
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple SQL select statements

    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.

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jennypretty View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member
    Join Date
    Jul 2008
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select Name from student where Name IN (select Dept from qry_student)
    Ian Anderson
    www.siteguru.co.uk

  6. #6
    Non-Member
    Join Date
    Jul 2008
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's it.
    I worked.
    Thanks very much.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    thanks, ian

    apparently my explanation in post #3 of what was wrong was too subtle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ian Anderson
    www.siteguru.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •