Multitable Join

Results 1 to 2 of 2

Thread: Multitable Join

  1. #1
    Join Date
    Dec 1969

    Default Multitable Join

    I&#039;m having trouble with my join syntax. Could anyone help me? I am trying to join three tables with a common field of pubId.<BR><BR>Thanks,<BR><BR>SQL = "SELECT, d.pubTitle, d.pubImage, d.pubFile, d.pubId, d.pubDesc, t.pubType, v.div from pubDetail d, pubType t "&_<BR>"LEFT JOIN pubDiv v ON v.divId = d.divId "&_<BR>" and t.pubId = d.pubId "&_<BR>" where v.divId = " & Request.Querystring("divId") & " " &_<BR>" order by d.pubTitle "<BR><BR>Error Message<BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80040e14&#039;<BR><BR>[Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.<BR><BR>detail_div.asp, line 34

  2. #2
    Join Date
    Dec 1969

    Default See my other answer...

    ...above about not mixing implicit and explicit joins!<BR><BR>You have the same problem!<BR><BR>Except here, since you are doing an outer join, you have NO CHOICE except to use an explicit join.<BR><BR>Also, you MUST put parens around multiple conditions of an ON.<BR><BR>In other words, the form *MUST* be<BR><BR>SELECT ...<BR>FROM ( A INNER JOIN B ON ( A.x = B.x ) )<BR> LEFT JOIN C ON ( C.y = B.y AND C.z = A.z ) <BR>...<BR><BR>So, I *THINK* this is what you are after:<BR><BR>SQL = "SELECT, d.pubTitle, d.pubImage, d.pubFile, d.pubId," _<BR> & " d.pubDesc, t.pubType, v.div " _<BR> & " FROM ( pubDetail d INNER JOIN pubType t ON (t.pubId = d.pubId) ) " _<BR> & " LEFT JOIN pubDiv v ON ( " _<BR> & " v.divId = d.divId " _<BR> & " AND v.divId = " & Request.Querystring("divId") _<BR> & " ) " _<BR> & " ORDER BY d.pubTitle "<BR><BR>Although this seems really strange to me, putting a condition on the maybe-it-will-be-in-the-record-and-maybe-it-won&#039;t "v" table.<BR><BR>You do realize that you have asked for *ALL* records from the INNER JOIN of "d" and "t" but only those from "v" that happen to match?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts