Exchange a subselect with appropriate syntax in mySQL

[Haishen]

Hi!

I just can't figure out how to re-write this Query so that it fits mySql, can someone plz help me!!!

SELECT * FROM Object WHERE name NOT IN((SELECT objectName FROM Booking WHERE startDate >= ? AND startDate < ? OR stopDate > ? AND stopDate <= ? OR startDate <= ? AND stopDate >= ?))

Obove String is a PreparedStatement, which means that the ?:s will be exchanged with appropriate values!!

Anyone??? Thanx // Kowalski

 

[Bromrrrrr]

Hmm well I'm not sure about your DB setup (or about my solution really ) but I'd try something like:

SELECT *
FROM Object O
LEFT JOIN Booking B ON (B.ObjectName = O.Name AND (B.startDate >= ? AND B.startDate < ? OR B.stopDate > ? AND B.stopDate <= ? OR B.startDate <= ? AND B.stopDate >= ?))
WHERE B.ObjectName IS NULL

Hope this helps,

Bromrrrrr

 

[Haishen]

Thanx Bromrrrrr for your suggestion, but unfortunately it semms to return all objects in the table...

...hmmm either I get some clever solution to this or i'll simply have to loose my face and re-write into two Queries !!!

Thanks a bunch anyway! // Haishen

 

[Haishen]

To Bromrrrrr!!!

Sorry man, I was dead wrong...the code worked perfectly...thank you! // Kowalski