Re: [Wpigdc-cms-development] MySQL doesn't support subqueries

[Oleg R. <sv....@ve...>]

(using a different e-mail address in the From: field, if you reply to
that address, I will get very upset).

On Thursday, August 09, 2001, 4:21:00 AM, in message
"[Wpigdc-cms-development] MySQL doesn't support subqueries", Liam
wrote:

LM> OK. So. It's 3:45 AM, so go easy on me.

LM> Here's what I want to do. We want to sort events by their start
LM> date. Unfortunately, events themselves don't have a start date in
LM> the db; their sub-items (or whatever you want to call it) do.
LM> (Read the previous email to understand). This wouldn't be a
LM> problem, if MySQL supported complex joins. But it doesn't. Just
LM> the simple stuff.

MySQL supports pretty damn complex joins, it does not support
sub-selects.

LM> Here's what I want to do: I want to select the earliest date item
LM> from the cms_event table where the cms_event_group foreign key
LM> corresponds to that particular event group, like so:

LM> select start_date from event_item where event_id = [main_event_id]
LM> order by start_date limit 1;

LM> (if there's a better way, please let me know.)

SELECT MIN(start_date) FROM event_item WHERE event_id = [main_event_id]

LM> then, I want to tack that start date onto the event_group table so
LM> I can sort by it. And I'd like to do that in 1 SQL statement. More
LM> complex databases will let you do that. However, it doesn't seem
LM> like MySQL supports something like that (PLEASE correct me if I'm
LM> wrong, and show me the proper syntax).

How about:

SELECT cms_event_group.cms_id, cms_event_group.cms_name,
       ...[as many fields from cms_event_group as you need]...,
       MIN(cms_event.cms_start_date)
    FROM cms_event_group, cms_event
    WHERE cms_event_group.cms_id = cms_event.cms_event_group

Let me know if that works...
       
LM> So. A few choices. d. every time we want to sort by start date for
LM> the event group (which is the most common field for sorting),
LM> create a new temporary table that does the same as the join. Then,
LM> when finished, delete the table. This is expensive, and if
LM> something goes wrong and the tables don't get deleted, wasteful.
LM> People will start DOS'ing our ass simply by hitting "Refresh" on
LM> the events page. Ok, I don't know if it's that expensive, maybe
LM> it's worth finding out.

MySQL has a few choices for temporary tables that are automatically
deleted at the end of the session (that's the definition of
"temporary"), including a "memory" table that doesn't even get written
to disk.

LM> e. Forget sorting using the database: sort in the object model.
LM> Certainly possible, but it doesn't work with the current setup
LM> using DBIterator at all. It's also most likely much slower than
LM> sorting in the db.

Ugh, won't touch the issue....