(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

umm Typo?

[chatworthy.livejournal.com]

I'm more accustomed to Oracle outer join syntax than ANSI so I could be wrong, but it looks like you're actually left-joining B with D.

Didn't you want to left-join A with D?

And if the symptom is that you're getting every row of A repeated for every result lot, that's called a cartesian join and it means you need an extra condition to restrict A.

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.