Re: Complicated Inner Joins

Wed, 14 Apr 2004 14:00:34 -0700

You need to drop the parenthesis. It looks you are trying to do a join on a sub-join, and you appear to be missing a join condition. Or you're trying to create a join with multiple conditions, which if fine. Near as I can tell, you've made this a lot more complicated than it should. I'm not sure I know what you are trying to do.

Try this:
SELECT i.*, p.ProjectName, si.OrderBy, si.editable, tbl_SubProject.SubProjectName
FROM tbl_Project AS p
INNER JOIN tbl_Invoice AS i ON p.ProjectID = i.ProjectID)
INNER JOIN tbl_InvoiceStatus AS si ON si.InvoiceStatus = i.InvoiceStatus
LEFT JOIN tbl_SubProject ON i.SubProjectID = tbl_SubProject.SubProjectID
WHERE i.InvoiceStatus = '#strInvoiceStatus#'
AND i.ProjectID =#intProjectID#
AND i.SubProjectID =#intSubProjectID#
AND i.Deleted = 0
Order BY si.Orderby,i.InvoiceNumber

I moved your tbl_InvoiceStatus join to after the tbl_Invoice join. I think using alias names for your tables is throwing you off since it's affecting your join order. Meaning, to read it properly you need to alias the table before you reference it in any join condition.


On Apr 14, 2004, at 4:01 PM, zzapper wrote:

Hi,

I've moved a Database Application from MSAccess to Mysql, most of the
application seems to work fine, but I'm having trouble with Inner
Joins. (The scripting language is ColdFusion MX 6.1) mySql 4.018.

Any help desperately welcome, including explanation of the principles


SELECT i.*, p.ProjectName, si.OrderBy, si.editable,
tbl_SubProject.SubProjectName
  FROM (tbl_Project AS p INNER JOIN
        (tbl_InvoiceStatus AS si
          INNER JOIN tbl_Invoice AS i
          ON si.InvoiceStatus = i.InvoiceStatus)
      ON p.ProjectID = i.ProjectID)
          LEFT JOIN tbl_SubProject
          ON i.SubProjectID = tbl_SubProject.SubProjectID
  WHERE i.InvoiceStatus = '#strInvoiceStatus#'
  AND i.ProjectID =#intProjectID#
  AND i.SubProjectID =#intSubProjectID#
  AND i.Deleted = 0
  Order BY si.Orderby,i.InvoiceNumber

General error: You have an error in your SQL syntax near
'(tbl_invoicestatus AS si INNER JOIN tbl_invoice AS i ON si.Invoice'
at line 3

SELECT i.*, p.ProjectName, si.OrderBy, si.editable,
tbl_subproject.SubProjectName FROM (tbl_project AS p INNER JOIN
(tbl_invoicestatus AS si INNER JOIN tbl_invoice AS i ON
si.InvoiceStatus = i.InvoiceStatus) ON p.ProjectID = i.ProjectID) LEFT
JOIN tbl_subproject ON i.SubProjectID = tbl_subproject.SubProjectID
WHERE i.InvoiceStatus Not IN('Show All') AND i.ProjectID =-1 AND
i.Deleted = 0 Order BY si.Orderby ,i.InvoiceNumber

zzapper (vim, cygwin, wiki & zsh)
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to