[Opensource] Question for you SQL experts.

[Malcolm Wise]

Mike,

> 2 - I believe that my issue with the mixing LEFT/RIGHT/INNER
> joins with
> equality joins is that the tables in the equality joins need
> to be listed
> first in the FROM portion.  Probably a SapDB 7.3 parser
> quirk.  I haven't
> tried it on the other databases yet.

This certainly isn't the case with Sybase.  The order of tables for equality
joins in the FROM clause makes no difference.

> //4 tables. two ANSI joins, one equality join
>
> SELECT COUNT( DOWNLOADLOG.LogEntry) FROM USERSTABLE,
> DOWNLOADLOG LEFT JOIN
> DOWNLOADFILES ON DOWNLOADLOG.FileNumber =
> DOWNLOADFILES.FileNumber  RIGHT
> JOIN MIMETYPES ON DOWNLOADFILES.MimeNumber =
> MIMETYPES.MimeNumber  WHERE
> DOWNLOADLOG.LogEntry = 1 AND DOWNLOADLOG.ExpUid = USERSTABLE.ExpUid

This returns 1 (due to the WHERE clause).

> //4 tables. Three equality joins.
> SELECT COUNT( DOWNLOADLOG.LogEntry) FROM DOWNLOADLOG, DOWNLOADFILES,
> MIMETYPES, USERSTABLE WHERE DOWNLOADLOG.LogEntry = 1 AND
> DOWNLOADLOG.FileNumber = DOWNLOADFILES.FileNumber AND
> DOWNLOADFILES.MimeNumber = MIMETYPES.MimeNumber AND
> DOWNLOADLOG.ExpUid =
> USERSTABLE.ExpUid

This returns 1.

> //3 tables, one left join, one equality join
> SELECT COUNT( DOWNLOADLOG.LogEntry) FROM MIMETYPES,
> DOWNLOADLOG LEFT JOIN
> DOWNLOADFILES ON DOWNLOADLOG.FileNumber =
> DOWNLOADFILES.FileNumber  WHERE
> DOWNLOADLOG.LogEntry = 1 AND DOWNLOADFILES.MimeNumber =
> MIMETYPES.MimeNumber

This returns - you guessed it - 1.

> [Total stupid question... this one returns 79... the size of
> the MimeTypes
> table... but I thought that would have to be a DownloadFiles
> RIGHT JOIN
> MimeTypes.....  so is this particular statement not doing what I
> want?]  I've tried parenthesis to separate sections of the
> JOIN statement,
> but Sap flat wouldn't cooperate.]

To return the size of the MIMETYPES table would normally require you to
leave out the join for MIMETYPES to DOWNLOADFILES or, as you say, doing a
RIGHT JOIN.  I get the size of my MIMETYPES table by removing  the 'AND
DOWNLOADFILES.MimeNumber = MIMETYPES.MimeNumber' part of the WHERE clause.
It would certainly seem that this statement isn't doing what it should.  The
following also gives me 4 (the number of rows in my MIMETYPES):

SELECT COUNT( DOWNLOADLOG.LogEntry) FROM  DOWNLOADLOG LEFT JOIN
(DOWNLOADFILES RIGHT JOIN MIMETYPES ON DOWNLOADFILES.MimeNumber =
MIMETYPES.MimeNumber)
 ON DOWNLOADLOG.FileNumber = DOWNLOADFILES.FileNumber

Just a thought, but if you have nothing confidential in there, could you
send me a script of the SQL INSERT statements so that I can re-produce your
data in my database?  That would ensure we can truly compare the results.
At the moment I'm just running against a few hacked entries in the
appropriate tables.

Cheers,
Malc




*************************************************************************
The information contained in this message or any of its
attachments may be privileged and confidential and intended 
for the exclusive use of the addressee. If you are not the
addressee any disclosure, reproduction, distribution or other
dissemination or use of this communication is strictly prohibited
*************************************************************************