current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I need to exclude second query (data from a second table) similar to MINUS. But since MySQL doesn't support MINUS, I decided to go with LEFT JOIN. However it gives me a database error.

enter image description here

Model

function searchRooms($room_type, $start_date, $end_date) {
// Line 6 is the below one:
    $query = $this->db->query(
        "SELECT     room_number
        FROM        rooms
        WHERE       room_type = '$room_type'
        ORDER BY    room_number ASC

        LEFT JOIN

        SELECT      room_number
        FROM        new_reservation
        WHERE       start_date >= '$start_date'
        AND         end_date <= '$end_date'
        ORDER BY    room_number ASC
        ");
    return $query->result();

}

Controller

function search() {
        $room_type = $this->input->post('room_type');
        $start_date = $this->input->post('start_date');
        $end_date = $this->input->post('end_date');
        $this->reservations_model->searchRooms($room_type, $start_date, $end_date);

        $this->index();
}
share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

if you want to exclude result from second query then

  SELECT room_number
    FROM rooms
   WHERE room_type = '$room_type'
     AND room_number not in (SELECT room_number
                          FROM new_reservation
                         WHERE start_date >= '$start_date'
                           AND end_date <= '$end_date')
ORDER BY room_number ASC

Using join instead of subquery

  SELECT a.room_number
    FROM rooms a left outer join (SELECT room_number
                          FROM new_reservation
                         WHERE start_date >= '$start_date'
                           AND end_date <= '$end_date') b
      ON a.room_number = b.room_number
   WHERE a.room_type = '$room_type'
     AND b.room_number is null
ORDER BY a.room_number ASC

Again if you don't want to use inline view

  SELECT a.room_number
    FROM rooms a left outer join new_reservation b
      ON a.room_number = b.room_number
   WHERE a.room_type = '$room_type'
     AND b.start_date >= '$start_date'
     AND b.end_date <= '$end_date'
     AND b.room_number is null
ORDER BY a.room_number ASC
share|improve this answer
1  
subqueries inside where clauses are very, very slow. –  STT LCU Jul 8 '13 at 15:26
1  
it depends on the cardinality, index, execution plan, etc –  chetan Jul 8 '13 at 15:29
1  
yes. But still, 99% of the time It's better to figure how to use a join instead of a subquery inside a where. –  STT LCU Jul 8 '13 at 15:30
1  
I doubt about your statistic. But as your wish, I am re-writing query using join –  chetan Jul 8 '13 at 15:33
    
OUTER JOIN worked. Thanks man! –  saucecord Jul 9 '13 at 0:19
add comment
up vote 3 down vote

Your syntax for a LEFT JOIN is wrong, it should be something in the form of:

SELECT     room_number
FROM        rooms
LEFT JOIN .... ON ...
WHERE  ...
ORDER BY ...

Or are you looking for a UNION instead of a LEFT JOIN?

Although in your case you could probably just invert the conditions of the second query and use that as a WHERE condition in the first query (if I understand you correctly...).

share|improve this answer
    
As user 'chetan' pointed out, I used LEFT OUTER JOIN and tested with few values as well. So far the results are accurate. Currently, testing further. –  saucecord Jul 9 '13 at 0:27
add comment
up vote 1 down vote

In MySQL, you JOIN tables, not SELECT statements. LEFT JOIN is used when the JOINed table may not have a matching row in it.

You want to JOIN and select only the rows you want.

Try this:

SELECT rooms.room_number
FROM rooms
JOIN new_reservation ON rooms.room_number = new_reservation.room_number
WHERE rooms.room_type = '$room_type'
AND NOT (start_date >= '$start_date' AND end_date <= '$end_date')
ORDER BY room_number ASC
share|improve this answer
    
As user 'chetan' pointed out, I used LEFT OUTER JOIN and tested with few values as well. So far the results are accurate. Currently, testing further. –  saucecord Jul 9 '13 at 0:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.