(no subject)

[nolly]

MySQL is kicking my tail.


Here's the query so far:
select d.scheduleDigest, c.userID, c.initDate, a.classroomID, b.startDate, b.stopDate, c.benchmarkName, a.benchmarkDigest, b.part, b.sessionNumber, c.fbDigest
from benchmarkClassrooms as a, benchmarks as c, benchmarkSessions as b LEFT JOIN schedule as d
ON b.benchmarkDigest = d.benchmarkDigest and b.sessionNumber = d.benchmarkSession and b.part = d.benchmarkPart
where a.benchmarkDigest = b.benchmarkDigest and a.benchmarkDigest = c.benchmarkDigest and a.clientID = 'darkmatter' and c.msgPrior >= to_days(b.startDate) - to_days(NOW()) and b.stopDate >= NOW();

(well, there's a bit more to the full query, but this'll do for now.)

The problem: It's, er, multiplexing on classroomID. (I'm sure there's a technical term for that, but I don't know/have forgotten it.) I need, basically, a left join between a and d, on benchmarkDigest and classroomID. (And yes, it has to be a left join; ultimately, a where schduleDigest IS NULL will be added, as I'm looking for tests that have not been scheduled.) I can't seem to make this work. Is it possible in a single query, using MySQL 4.whatever? (that is to say, the current released version, 4.0.23 or whatever it is)

Point me at any SQL or MySQL communities you're aware of, too.

Comments

Re: umm Typo?

[nolly.livejournal.com]

I need to left join both a and b with d. I could probably accomplish this by using a temp table to join a and b, and left joining that with d, but it'd be much simpler to do this in one step.