Hello!

Can someone tell me if this SQL-query is valid!? I ran it on two
systems.

On a "MySQL 4.1.11-Debian_4sarge7" I get an result but really slow.

Running the same query on a MsSql or Mysql5 on windows returns errors.

MSSQL ERROR: The multi-part identifier could not be bound
MYSQL: unknown column 'eca.filename' in ON clause

SELECT oec.messageid, oec.sendtime, oec.esubject, zt.emailadresse,
eca.filename
FROM emailcontentattachement eca, emailcontent oec
INNER JOIN ( SELECT messageid, emailadresse, type from emailrelations
WHERE messageid IN
( SELECT ec.messageid FROM emailcontent ec INNER JOIN emailrelations
er ON ec.messageid=er.messageid
INNER JOIN emailuserrelation eur ON
(er.emailadresse=eur.emailadresse)
INNER JOIN members m ON (eur.member_name=m.member_name AND
m.member_name='username') ) ) zt ON (zt.messageid=oec.messageid) AND
eca.filename LIKE '%doc%'

On Thu, 27 Mar 2008 02:16:01 -0700 (PDT), stefan.keul@googlemail.com wrote:
> Hello!
>
> Can someone tell me if this SQL-query is valid!? I ran it on two
> systems.
>
> On a "MySQL 4.1.11-Debian_4sarge7" I get an result but really slow.

EXPN 'really slow'

Are we talking about hundreds and hundreds of ms or are we talking about
multiple hours?

> Running the same query on a MsSql or Mysql5 on windows returns errors.
>
> MSSQL ERROR: The multi-part identifier could not be bound
> MYSQL: unknown column 'eca.filename' in ON clause
>
> SELECT oec.messageid, oec.sendtime, oec.esubject, zt.emailadresse,
> eca.filename
> FROM emailcontentattachement eca, emailcontent oec
> INNER JOIN ( SELECT messageid, emailadresse, type from emailrelations
> WHERE messageid IN
> ( SELECT ec.messageid FROM emailcontent ec INNER JOIN emailrelations
> er ON ec.messageid=er.messageid
> INNER JOIN emailuserrelation eur ON
> (er.emailadresse=eur.emailadresse)
> INNER JOIN members m ON (eur.member_name=m.member_name AND
> m.member_name='username') ) ) zt ON (zt.messageid=oec.messageid) AND
> eca.filename LIKE '%doc%'

SHOW CREATE TABLE emailcontentattachement;

On both systems. I bet you find that that the tables are not the same.
Specifically, on of them doesn't have a column named `filename`.

--
Don't even get me started on the MCSEs I know. It's a miracle of
modern technology that some of these fsckwits still draw breath,
much less a paycheck.
-- Marc Bowden