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.

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.

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.:)

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.

Subqueries are generally faster than joins, FYI. Why don't you post the two select statements and we'll see if we can help.

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.

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???

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.

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.

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

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

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.

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 ASCproduces 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.:)

Corrections again! :)

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

Thanks.

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.

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.

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.

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.

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.

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.:)

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/upgrading-from-4-0.html