Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009

    Unanswered: Reverse a Condition

    In some programming languages, you can reverse a conditional statement with an operator.

    For example in php you can use !(condition producing true) to test for false.

    Is there anything in MySQL for this type of test?

    I have:
    WHERE property_active = 1 
    AND (reservation_end <= '2010-01-10' AND reservation_start >= '2010-01-05')
    And I want to reverse the date test.


  2. #2
    Join Date
    Jun 2007
    You have the options of using NOT or ! or just to reverse the test in the where clause. It's a good idea to full test the results though as many folks make mistakes.

  3. #3
    Join Date
    Dec 2009
    It's a good idea to full test the results though as many folks make mistakes.
    testing it is how I found out I am one of those "folks", the first test worked fine, when only one reservation per unit was in the db. Now I am getting properties back that I don't want because, if there is more than one reservation then I get a result back for the unit since a different reservation makes it qualify.

    So, I thought instead of selecting everything outside the date range, I just want to get all ids with a reservation in the range, group it and then see if the id is in the result.

    Here is an example, maybe I am making this harder than it is, but if there is a reservation for dates:

    2010-03-19 through 2010-03-26 It doesn't get filtered. I am trying to cover every possible date combination, but i think I am missing a couple.

    namely: when a submitted start date is greater than the existing reservation's start date. Ugh...

    SELECT *,
    COALESCE( t.type_name, 'None Given' ) type_name, p.property_id property_id
    FROM (properties p) 
    LEFT JOIN reservations re ON re.property_id = p.property_id
    LEFT JOIN complexes c ON c.complex_id = p.complex_id
    LEFT JOIN TYPES t ON t.type_id = p.type_id
    LEFT JOIN vicinities v ON v.vicinity_id = p.vicinity_id
    LEFT JOIN destinations d ON d.destination_id = p.destination_id
    LEFT JOIN property_x_rate pxr ON pxr.property_id = p.property_id	
    LEFT JOIN rates r ON pxr.rate_id = r.rate_id	
    WHERE property_active = 1
    	AND property_approved = 1
    	AND p.property_id NOT IN(
    		SELECT p.property_id FROM reservations r
    		JOIN properties p using(property_id)
    			(reservation_end <= '2010-03-26' AND reservation_end > '2010-03-21') OR 
    			(reservation_start >= '2010-03-21' AND reservation_start < '2010-03-26')
    		GROUP BY p.property_id
    	AND p.property_id IN(
    		SELECT p.property_id FROM properties p 
    		JOIN property_x_amenity pxa using(property_id)
    		JOIN amenities a using(amenity_id)
    		WHERE pxa.amenity_id IN (3)
    		GROUP BY pxa.property_id HAVING COUNT(pxa.property_id) = 1
    	)GROUP BY p.property_id
    I can't use BETWEEN because, the end date cannot be included in the range since the arrival and the departure can occur on the same date.
    Last edited by wowdezign; 01-20-10 at 16:11.

  4. #4
    Join Date
    Jun 2007
    Just looking at your SQL I can make a few points:
    From a logic perspective:
    • The test on dates is not correct at all and doesn't cover many scenarios.
    • You don't appear to have any tests on location or price yet I'd of thought this would be the primary search for any user.

    From a performance perspective:
    • In the subqueries you are joining with Properties but this isn't necessary.
    • You are also joining with every table in the database but I can't see why you need all this data at this point.
    • You are using IN rather than EXISTS which is much slower.

    From a maintenance perspective:
    • You are using SELECT * means your query will return every field in every table. Apart from affecting performance this will also make your program more difficult to write - you'll get multiple fields all containing property_id etc. Also if you add a field to any table then suddenly your query will return a new field which will upset your calling program. Better to just pull the fields you need.
    • You sometimes join using the USING syntax and sometimes using the ON syntax - you should decide to stick with one method or the other to make the code more readable.
    • You're using GROUP BY but not using any aggregate function - this looks wrong to me and will most likely confuse you/others if they look at the code in future.
    • You give an alias to the table Properties but then don't use it which makes it difficult to see which tables a field has come from ie WHERE property_active = 1.
    • The code looks messy - I can imagine you looking at that in 6 months time and wondering what the hell is going on.

Posting Permissions

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