[Opensource] Question for you SQL experts.

[Michael Rimov]

At 12:10 PM 6/9/2003 +0100, you wrote:

>Mike,
>
>The main problem I can see with the SQL generated is that when you specify a
>join in the FROM clause, you shouldn't repeat that in the WHERE clause, i.e

Malc,

Thanks so much for looking it over.

1 - Thanks for the hint on not repeating things in the where clause.

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.



>SELECT COUNT( DOWNLOADLOG.LogEntry) FROM
>DOWNLOADLOG,DOWNLOADFILES  LEFT
>JOIN MIMETYPES ON DOWNLOADFILES.MimeNumber =
>MIMETYPES.MimeNumber WHERE
>DOWNLOADLOG.LogEntry = 1 AND DOWNLOADLOG.FileNumber =
>DOWNLOADFILES.FileNumber AND DOWNLOADFILES.MimeNumber =
>MIMETYPES.MimeNumber
>
>should I think be:
>
>SELECT COUNT( DOWNLOADLOG.LogEntry) FROM
>DOWNLOADLOG,DOWNLOADFILES  LEFT
>JOIN MIMETYPES ON DOWNLOADFILES.MimeNumber =
>MIMETYPES.MimeNumber WHERE
>DOWNLOADLOG.LogEntry = 1 AND DOWNLOADLOG.FileNumber =
>DOWNLOADFILES.FileNumber

Done.


>Incidently, why are you doing a LEFT JOIN between DOWNLOADFILES and
>MIMETYPES?  DOWNLOADFILES.MimeNumber is non-nullable, right? [Apologies if
>you're just playing with the syntax and not trying to get any meaningful
>results :)]

I'm just playing with syntax to make sure that the JoinedDataObject is 
autogenerating syntactically correct statements given my XML data file.


<snip> <snip>

>My database is Sybase ASE12.  Incidently, what error does SAPDB give you?  I
>can run all of the SQL you specify in your mail without any syntax errors.
>I think the reason all your results return 1 is that you specify
>DOWNLOADLOG.LogEntry = 1 in all of your WHERE clauses.  If you remove this,
>DOWNLOADLOG LEFT JOIN DOWNLOADFILES on FileNumber should return 5 and
>DOWNLOADLOG JOIN DOWNLOADFILES on FileNumber should return the number of
>rows in DOWNLOADLOG that have matches in DOWNLOADFILES.
>I guess this covers questions 1 and 2,  not sure if this answers 3 though.
>I've had to write plenty of SQL using ANSI JOIN syntax lately, so I'll
>attempt to answer any more you may have.

Well, I have at least code executing.

So, if you could double-check the following SQL statements to make sure 
they at least execute on Sybase, I'd be grateful!  I'm just using the count 
portion  to keep the SELECT statement minimal here.

//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

//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

//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

[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.]

Anyway, again, I appreciate your help.
                                                         -Mike