How to get all rows where value in table does not exist in mysql

Question

Ok I am really stuck. I have three tables as follows:

table contracts

contract_id  |  hotel_id  |  start    |    end
---------------------------------------------------
    1              356     2012-12-12   2012-12-16   
    2              258     2012-12-12   2012-12-16   
    3              211     2012-12-12   2012-12-16

table hotel_info

hotel_id  |  hotel_desc
------------------------------
   356           description 1
   258           description 2
   211           description 3

table rates

contract_id  |  hotel_id  |  book_date  |   rate   |   closed  
--------------------------------------------------------------
     1            258       2012-12-12     250.00        1
     1            258       2012-12-13     250.00        0
     1            258       2012-12-14     250.00        1
     1            258       2012-12-15     250.00        1

Now what I am trying to do is retrieve all the rows from contracts table between a certain date range that the user will input. As you can see the rates table is seperated by each individual date. I do not want to get any rows back if the rates associated to them have a 0. So for example if I wanted to get a rate for dates between 2012-12-14 and 2012-12-15 I would get a result. But if I searched for 2012-12-12 to 2012-12-14 I do not get a result. I dont know if this is mega confusing but here is the mysql I have so far, which is not working:

SELECT r.*, c.* FROM contracts AS c
INNER JOIN hotel_info AS r ON r.hotel_id = c.hotel_id
INNER JOIN rates AS ra ON ra.contract_id = c.contract_id AND ra.closed != 0
WHERE c.start <= '2012-12-12' AND c.end >= '2012-12-16' GROUP BY r.room_name

Any help on this would be so appreciated! Let me know if I am missing something. Thannks in advance!

in rates table book_date is not the same as i ncontracts start end date. You want to get dates for if I wanted to get a rate for dates between 2012-12-14 and 2012-12-15 I would get a result. But if I searched for 2012-12-12 to 2012-12-14 I do not get a result if you search like that dates in contracts table you don't get data at all. Now in your Query you searching not between dates but opposite.... — Justin
– Justin, Commented Nov 13, 2012 at 6:50
how do you join contracts and rates? by contract_id, hotel_id, or both? — fthiella
– fthiella, Commented Nov 13, 2012 at 10:34

Dave Zych · Accepted Answer · 2012-11-13 04:46:12Z

2

If I'm understanding this correctly, you only want to return data if the range of the associated rate records within the date range specified for the contract have a rate of 1. I think using NOT EXISTS might do what you want.

SELECT r.*, c.* FROM contracts AS c
    INNER JOIN hotel_info AS r ON r.hotel_id = c.hotel_id
    INNER JOIN rates AS ra ON ra.contract_id = c.contract_id AND ra.closed != 0
WHERE c.start >= '2012-12-12' AND c.end <= '2012-12-16' 
    AND NOT EXISTS 
     (
         SELECT
             1
         FROM rates ira 
         WHERE ira.contract_id = ra.contract_id
             AND ira.rate = 0
     )
GROUP BY r.room_name

answered Nov 13, 2012 at 4:46

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get all rows where value in table does not exist in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related