Fog Creek Software
Discussion Board




More SQL

Id like to verify something with you guys.

I got a job where I use MySQL, in a version that does not support subselects. The two tables that are relevant to this query are one that has a list of items (tItem), and one that has dates when the item are "booked" (tSchedule). The users will use this app to schedule their use of the items.
I need to get a list of all items that are not already taken for a specific period. Normaly I would do this with a subselect in the where clause (...WHERE NOT IN (SELE...) but this doesnt work here.

This is what Ive come up with:

SELECT tItem.ID FROM tItem
LEFT JOIN tSchedule ON tItem.ID = tSchedule.itemID
WHERE ((date_start IS NULL AND date_end IS NULL)
OR NOT ((date_start BETWEEN '2003-05-05' AND '2003-11-06' )
OR (date_end BETWEEN '2003-05-05' AND '2003-11-06')));

Its messy. If the item is not referenced, or it is, but the start/end dates are not within the period, it goes into the result set.

It look like its working as expected, but quite frankly Im not 100% sure about how. If I remove the IS NULL stuff it stops working, and to my logic, it shouldnt.
Im thinking about doing this with two separate queries and do the culling in the code, but the lists will be in the order of 2000 items so it would be a performance hit.

What do you think.

Eric Debois
Friday, August 08, 2003

LEFT JOIN creates an outer JOIN, I would suggest you join these tables using an INNER JOIN instead.

Patrik
Friday, August 08, 2003

How about using a real database?

Common sense
Friday, August 08, 2003

I would think you would want this:

SELECT tItem.ID FROM tItem
LEFT JOIN tSchedule ON tItem.ID = tSchedule.itemID
WHERE  (date_start BETWEEN '2003-05-05' AND '2003-11-06' )
AND (date_end BETWEEN '2003-05-05' AND '2003-11-06');
AND tSchedule.itemID IS NULL

In other words, left join tItem only against those records which would make it ineligible, and then tell me which records failed to join anything.

Haven't tested the query against any results, and I don't know MySql specifically, so I can't guarantee this is right.

Keith Wright
Friday, August 08, 2003

I should add that I think your query fails.  I believe it will return a record even if it is checked out, as long as it has also been checked out for some other period.

Keith Wright
Friday, August 08, 2003

Keith, good spot. You are absolutly right. Thanks.

Eric Debois
Friday, August 08, 2003

Can you use temp tables?  You can load your sub select results into a temp table, then do a join on that.

CharlesC
Friday, August 08, 2003

The problem with using a JOIN instead of a sub-select is that you will return multiple, duplicate records of titles if they happened to be checked out more than once in the given time period.
The only way I can think of to accomplish what you want is to use a temp table. I do not think that the performance hit will be that great (because I believe that the way other RDBMS's implement sub-selects internally is with a behind-the-scenes temp table). Also, it is probably more efficient to use the temp table *because* you have a large result set.

Does anyone know how to return only unique Title records withOUT using a JOIN, a sub-select, or a temp table (besides the troll-tastic "use a real database")?

Jordan Lev
Friday, August 08, 2003

I think my query should work fine.  I only allow items to join check-out records that would disqualify it from our desired results.  I then filter out any of the records in the Left Join which have succeeded in joining something, leaving only the available items remaining, and only once, since unmatched items in a left join will only show up once.

Keith Wright
Friday, August 08, 2003

Actually, I couldnt get your idea to work Keith. I diddnt try very long though. I made a kludgy thing with two queries instead. At least its easy to understand, and pretty foolproof, if not very elegant.

Eric Debois
Friday, August 08, 2003

*  Recent Topics

*  Fog Creek Home