SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    nested select possible?

    Hi folks,

    I have a query here. Is it possible to do a select within a select? How to I get that done then? Thanks in advance.

    $sql_resume1 = "SELECT ref_no,name,country,em_type,min_salary,display_post_date FROM resume_t where em_type IN ('$_POST[emp_type_EMP_TYPE_1]','$_POST[emp_type_EMP_TYPE_2]','$_POST[emp_type_EMP_TYPE_3]','$_POST[emp_type_EMP_TYPE_4]','$_POST[emp_type_EMP_TYPE_5]')";

    $sql_resume2 = "SELECT * FROM ($sql_resume1) where pri_job IN ('Internet/New Media','Other') order by post_date desc";

    $result = mysql_query($sql_resume2) or die("SELECT Error: ".mysql_error());


    Please note that above is only an example. The sql query I intend to do has more selects within selects, perhaps $sql_resume3,4,5...so on.

    Thanks
    Regards,
    Junk

    I am never more keen to learn...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select ref_no
         , name
         , country
         , em_type
         , min_salary
         , display_post_date 
      from resume_t 
     where em_type in 
             ('$_POST[emp_type_EMP_TYPE_1]'
            , '$_POST[emp_type_EMP_TYPE_2]'
            , '$_POST[emp_type_EMP_TYPE_3]'
            , '$_POST[emp_type_EMP_TYPE_4]'
            , '$_POST[emp_type_EMP_TYPE_5]')
       and pri_job in 
             ('Internet/New Media'
            , 'Other') 
    order 
        by display_post_date desc
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, thanks for your reply. Well, in my example, yes, your suggested query will do the job. Pardon me, maybe I am not too clear. Ok, there will be some mysql select queries like

    <?php if $selected1 == 'all' { ?>
    $sql_resume1 = "SELECT * FROM resume_t where pri_job IN ('Internet/New Media','Other')";
    <?php } ?>

    <?php if $selected1 != 'all' { ?>
    $sql_resume1 = "SELECT * FROM resume_t where pri_job IN ('Media','Engineering)";
    <?php } ?>


    <?php if $selected2 == 'all' { ?>
    $sql_resume2 = "SELECT * FROM ($sql_resume1) where emp_type IN ('Part time','Temp')";
    <?php } ?>

    <?php if $selected2 != 'all' { ?>
    $sql_resume2 = "SELECT * FROM ($sql_resume1) where emp_type IN ('Full time','Intern')";
    <?php } ?>

    <?php if $selected3 == 'all' { ?>
    $sql_resume3 = "SELECT * FROM ($sql_resume2) where lang IN ('English','Japanese')";
    <?php } ?>

    <?php if $selected3 != 'all' { ?>
    $sql_resume3 = "SELECT * FROM ($sql_resume2) where lang IN ('Malay','Spanish')";
    <?php } ?>

    .....
    ....
    ....
    ....

    this goes on..

    So, I would like to know if there is any method to achieving what I wanted. Thanks in advance.
    Regards,
    Junk

    I am never more keen to learn...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, i see a bunch of different queries in a php script

    what was the question again?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, based on the values of the different php variables (eg. selected1, selected2, selected3), different mysql query will be choosen.

    If you can see the trend, if $selected1, $selected2 and $selected3 have value 'all', then I would want the mysql query to be


    $sql_resume1 = "SELECT * FROM resume_t where pri_job IN ('Internet/New Media','Other')";

    $sql_resume2 = "SELECT * FROM ($sql_resume1) where emp_type IN ('Part time','Temp')";

    $sql_resume3 = "SELECT * FROM ($sql_resume2) where lang IN ('English','Japanese')";

    whereas if the $selected1, $selected2 and $selected3 are ' ', then the query will be

    $sql_resume1 = "SELECT * FROM resume_t where pri_job IN ('Media','Engineering)";

    $sql_resume2 = "SELECT * FROM ($sql_resume1) where emp_type IN ('Full time','Intern')";

    $sql_resume3 = "SELECT * FROM ($sql_resume2) where lang IN ('Malay','Spanish')";

    I would like to know whether I can enjoy such flexibility for the sql query. Bascially is embeded queries and queries will be selected according to the value of the php variables.

    I know of something like

    select * from (select * from whatever_table where....) ...

    I would like to use similar method, but replacing the nested select with a php variable like this

    $php_variable = select * from whatever_table where...

    $sql_query = select * from ($php_variable) where...

    Hope I made myself clear this time. Thanks
    Regards,
    Junk

    I am never more keen to learn...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, no clearer than before

    why do you want to run three queries when you can run one? why won't you combine the WHERE Clauses like i did in post #2?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because the query selected is dependent on the values of the php variables. And the variables will differ for different users. Thus, I need to have such flexibility. Can nested sql query be done like the one I have stated? Thanks
    Regards,
    Junk

    I am never more keen to learn...

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Junk
    Can nested sql query be done like the one I have stated?
    i think so

    the first query will return all columns, filtered rows

    the next query will want to do a further filtering but only on the rows found in the first query

    (that's how i understood your concept, and i still think you should combine the queries, but if you want to run three in a row, okay then)

    the trick is, you have to use this syntax --
    Code:
    SELECT * FROM resume_t
     where emp_id in ( list of ids )
       and emp_type IN ('Part time','Temp')
    which means you are going back to the database, but only using the emp_id values that you already qualified by the first query

    or were you possibly thinking of subqueries? you're allowed to nest one query inside another, if you wish --
    Code:
    SELECT * FROM (
    SELECT * FROM (
    SELECT * FROM resume_t 
       where pri_job IN ('Internet/New Media','Other')
     ) where emp_type IN ('Part time','Temp')
     ) where lang IN ('English','Japanese')
    but that's actually the same as the one-query suggestion i had, where you consolidate all the WHERE conditions with ANDs
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    thanks for your reply.

    You have got the concept right,the first query will return all columns, filtered rows the next query will want to do a further filtering but only on the rows found in the first query.

    However, what I need is to separate the query into more parts. As in post #3. I understand that your sql statement is all conditions in one sql query. But I cannot hardcode this sql query for I know not of the values of the php variables. The sql query is dependent on the php variables, as I have said earlier. Unless this can be done


    $sql1 = "SELECT * FROM resume_t where pri_job IN ('Internet/New Media','Other')";

    $sql1 = $sql1."and emp_type IN ('Part time','Temp')";

    second query is a continual of the first.


    Thanks in advance
    Regards,
    Junk

    I am never more keen to learn...

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Junk
    Unless this can be done ...
    what do you mean, unless?

    of course it can be done

    or else i don't understand the question
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •