Reverse a Condition

wowdezign · #1 (**permalink**) 01-20-10, 13:53

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:

Code:

WHERE property_active = 1 
AND (reservation_end <= '2010-01-10' AND reservation_start >= '2010-01-05')

And I want to reverse the date test.

Thanks.

mike_bike_kite · #2 (**permalink**) 01-20-10, 14:57

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.

wowdezign · #3 (**permalink**) 01-20-10, 16:05

Quote:

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

Code:

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)
		WHERE 
			(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.

mike_bike_kite · #4 (**permalink**) 01-21-10, 04:09

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech