Results 1 to 20 of 20
  1. #1
    Join Date
    Sep 2002
    Location
    Bohol, Philippines
    Posts
    72

    Performing SELECT statements based on the result of another SELECT statement

    Hi!

    How can I perform a SELECT statement based on the results from another SELECT statement?

    For example, I perform a SELECT statement from a table. Based on that result, I perform another SELECT statement. Is that possible in MySQL?

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,715
    That's very... vague.

    If you pull the data into some other program, such as a PHP or perl script, you can do anything you want with it, including use the data to influence other queries. That holds true for any data storage system.

    Some databases also support embedding a SELECT inside another SELECT (known as a subselect). I believe MySQL 4.1 supports them, but not earlier versions.

    SQL also supports joins between tables so you can cross-reference data from one table with data from another (possibly several) table. Many people new to SQL use nested queries when a join would be faster and less error-prone.
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  3. #3
    Join Date
    Sep 2002
    Location
    Bohol, Philippines
    Posts
    72
    actually, I'm using PHP-MySQL and I already have done it and it was OK. What i'm concerned is that the result of the SELECT statement is too many and I want to filter them with another SELECT so that the data to be passed to PHP will already be lesser. In this way, it will be faster to go around with the data.

    If it will be possible for the SELECT to be embedded in another SELECT, it will be better. I saw one with SELECT embedded in the INSERT statement and it worked.

  4. #4
    Join Date
    Apr 2005
    Posts
    42
    Either you are referring to a sub query like:

    http://dev.mysql.com/doc/mysql/en/subqueries.html (only 4.1 release and up)

    Or views

    http://dev.mysql.com/doc/mysql/en/views.html (only available 5.0.1 release and up)

    These have very specific uses. It is very easy to write a really bad sub query that will kill the processor for a while.

    Also you might try using HAVING to filter on column values.

  5. #5
    Join Date
    Mar 2004
    Location
    california
    Posts
    162
    Subqueries are generally faster than joins, FYI. Why don't you post the two select statements and we'll see if we can help.

  6. #6
    Join Date
    Sep 2002
    Location
    Bohol, Philippines
    Posts
    72
    here's my table.

    What I actually wanted is that I want to display all the rooms that will be available for a certain date.

    Say for example, I want to display the rooms available for 2005-05-02 to 2005-05-05. The room that should be displayed is room no. 4 only since the other rooms are occupied during those days. I already have this SELECT statement:
    SELECT a.resdate,a.ndays,b.rmno,c.rmno,a.resno,c.resno FROM rooms AS b LEFT JOIN reservation AS a,reservedetail AS c ON ('2005-05-05' > a.resdate) and ('2005-05-02' < ADDDATE(a.resdate, INTERVAL a.ndays DAY)) and b.rmno=c.rmno and a.resno=c.resno ORDER BY b.rmno
    The problem with the statement is that it will return several records and I still have to manipulate them in PHP to filter the records.

    That's why I was thinking of another way wherein I have a SELECT statement inside a SELECT statement... or is there any other way?

    Thanks.
    Attached Thumbnails Attached Thumbnails tables.jpg  

  7. #7
    Join Date
    Apr 2005
    Posts
    42
    Your trying to put too much in your join and you need to join on something like key, like a primary to a forgein key, to make the query efficient.

    eg.

    select
    your fields...
    FROM
    your table t1
    JOIN
    another table t2 ON t1.id = t2.id
    WHERE
    date range stuff
    ORDER BY
    field name

    What can you join rooms and reservation tables on???

  8. #8
    Join Date
    Apr 2005
    Posts
    42
    didnt see the table layout.

    This will need to be debugged.

    select
    resd.dtl_no as reservation_detail_id,
    rooms.rmno as room_number,
    rooms.rmtype as room_type,
    res.custno as customer_number,
    res.resdate as reservation_date,
    res.ndays as number_days

    FROM
    (reservationdetail resd JOIN reservation res ON res.resno =res.resno)
    JOIN rooms ON rooms.rmno = resd.rmno
    WHERE
    res.resdate >= 'DATE_FROM'
    AND
    res.resdate <= ADDDATE(res.resdate, INTERVAL res.ndays DAY))
    ORDER BY
    rooms.rmno ASC


    also if the table you posted is teh sample data you are workign with, this should return zero results. because the resno keys dont match.

  9. #9
    Join Date
    Sep 2002
    Location
    Bohol, Philippines
    Posts
    72
    Ohh sorrryyy I corrected the tables. I'm attaching the corrections here.

    reservedetail relates to reservation thru resno and reservedetail relates to rooms thru rmno.

    now, how will I create the SELECT...JOIN statement to display all rooms available from a given date range? Thanks.
    Attached Thumbnails Attached Thumbnails tables.jpg  

  10. #10
    Join Date
    Apr 2005
    Posts
    42
    Then just change it to:

    SELECT resd.dtl_no AS reservation_detail_id, rooms.rmno AS room_number, rooms.rmtype AS room_type, res.custno AS customer_number, res.resdate AS reservation_date, res.ndays AS number_days
    FROM (
    reservedetail resd
    JOIN reservation res ON res.resno = res.resno
    )
    JOIN rooms ON rooms.rmno = resd.rmno
    WHERE res.resdate >= '2005-05-02'
    AND ADDDATE( res.resdate, INTERVAL res.ndays
    DAY ) <= '2005-05-10'
    ORDER BY rooms.rmno ASC
    LIMIT 0 , 30

  11. #11
    Join Date
    Apr 2005
    Posts
    42
    was doing a join on itself, hehe:

    this is right:


    SELECT resd.dtl_no AS reservation_detail_id, rooms.rmno AS room_number, rooms.rmtype AS room_type, res.custno AS customer_number, res.resdate AS reservation_date, res.ndays AS number_days
    FROM (
    reservedetail resd
    JOIN reservation res ON resd.resno = res.resno
    )
    JOIN rooms ON rooms.rmno = resd.rmno
    WHERE res.resdate >= '2005-05-02'
    AND ADDDATE( res.resdate, INTERVAL res.ndays
    DAY ) <= '2005-05-11'
    ORDER BY rooms.rmno ASC

  12. #12
    Join Date
    Jan 2002
    Location
    Kuwait
    Posts
    679
    As an alternative to subquries, you can create a temporary table and populate it with the results of the first SELECT and then do the second SELECT on it.
    Ahmad Alhashemi
    PHP, Apache, C, Python, Perl, SQL
    18 related BrainBench certificates

  13. #13
    Join Date
    Sep 2002
    Location
    Bohol, Philippines
    Posts
    72
    Hi!

    The result of the query suggested by Insert_Name_Here:
    SELECT resd.dtl_no AS reservation_detail_id, rooms.rmno AS room_number, rooms.rmtype AS room_type, res.custno AS customer_number, res.resdate AS reservation_date, res.ndays AS number_days
    FROM (
    reservedetail resd
    JOIN reservation res ON resd.resno = res.resno
    )
    JOIN rooms ON rooms.rmno = resd.rmno
    WHERE res.resdate >= '2005-05-02'
    AND ADDDATE( res.resdate, INTERVAL res.ndays
    DAY ) <= '2005-05-05'
    ORDER BY rooms.rmno ASC
    produces NOTHING. There should be 1 result and that is rm no. 4 since that's the only vacant from 2005-05-02 to 2005-05-05.

    I was using the LEFT JOIN in my first query so that all rooms will be displayed and I will just do another SELECT to filter them more.

    Ahmad's suggestion of using a temp table is a good alternative but if it can be done using a single SELECT query then the better. Thanks.

  14. #14
    Join Date
    Sep 2002
    Location
    Bohol, Philippines
    Posts
    72
    Corrections again!

    The query produces rm nos. 2 and 3 instead of rm no. 4 only.

    Thanks.

  15. #15
    I am not (yet) a mysql expert, but it has been my experience in learning so far that if I have to do any string manipulation of query results in php, I havent really asked mysql for what I really want.

    Seems that the best SQL queries I write answer a very specific question and return only what I am looking for. If thats not the case, I have to rethink what I am trying to do.

    An added bonus is that it makes your application code tons shorter and potentially more portable as well.
    "The only difference between a poor person and a rich person is what they do in their spare time."
    "If youth is wasted on the young, then retirement is wasted on the old"

  16. #16
    If you're using mysql4.1 or higher you can try this query. The query's efficiency is questionable however I assume you 've added indexes etc to the tables.
    Code:
    SELECT rmno FROM rooms WHERE rmno NOT IN 
    (SELECT r1.rmno FROM rooms r1 
    LEFT JOIN reservedetail rd ON r1.rmno=rd.rmno 
    LEFT JOIN reservation r2 ON rd.resno=r2.resno 
    WHERE 
    '2005-05-02' BETWEEN r2.resdate 
    AND (DATE_ADD(r2.resdate, INTERVAL r2.ndays DAY)) 
    OR 
    '2005-05-05' BETWEEN r2.resdate 
    AND (DATE_ADD(r2.resdate, INTERVAL r2.ndays DAY) ) 
    );
    You could do the SUBQUERY first and then use PHP to implode the list and put it in the query if you don't have mysql4.1. It could end up being faster regardless. You'd have to do your own tests.

    ie SELECT rmno FROM rooms WHERE rmno NOT IN($imploded_list);

    The reason the query is like this is due to the fact that a reservation can reserve multiple rooms.

  17. #17
    Join Date
    Sep 2002
    Location
    Bohol, Philippines
    Posts
    72
    Actually, I already have records produced based on the SELECT statement I posted earlier. My problem is that they produce several records and I still have to manipulate them in PHP to get the available rooms for that period of time.

    If it will be possible for a single SELECT statement to produce the available rooms, the better, so that the script for PHP will only do the looping and no more conditionals. In this way, the execution will be faster.

    As what I have understood, MySQL works faster than PHP. Am I right? Thanks.

  18. #18
    You and I posted about the same time, so you may have missed my post above.

    To answer the question on Mysql being faster than PHP. In my experience with PHP and MYSQL, In MANY cases Mysql can/will be faster if you take advantage of the functions etc available to you and formulate the query properly.

    However, It cannot be assumed that Mysql will be faster. My tests with SUBQUERIES has shown more times than not that doing 2 properly formed queries is better than doing a SUBQUERY or MULTIPLE Subqueries.

    I could be at fault in my formulation of the queries, but in some instances I've tried for a very long time to get multiple Subqueries to be faster and it hasn't proven to be.

    Testing is the best thing to do to figure out which way is best with the knowledge available to you.

  19. #19
    Join Date
    Sep 2002
    Location
    Bohol, Philippines
    Posts
    72
    Thanks seiken. we actually posted at the same time.

    Anyway, I will be testing the QUERY you posted. I still have to upgrade my MySQL because it is still ver. 4.0 and I guess your QUERY will not run on lower versions.

    I'll keep posted if the query will function correctly so others will also be updated.

  20. #20
    There are a couple of things you'll want to keep in mind if your going to upgrade.
    http://dev.mysql.com/doc/mysql/en/up...-from-4-0.html

Posting Permissions

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