INNER JOINS 1

[coldfused]

Guys I'm trying to pull data from 4 tables that match a URL paramater.

I canb get the data from 3 tables like this:

SELECT *
FROM (Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.OrderItemOrderID)
INNER JOIN Members ON Members.ID = Orders.OrderMemID
WHERE Orders.OrderID = '#URL.OrderID#'

But when I try to match up the fourth table:

SELECT *
FROM (Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.OrderItemOrderID)
INNER JOIN Members ON Members.ID = Orders.OrderMemID
INNER JOIN Products ON Products.ProductID = OrderItemProdID.ID
WHERE Orders.OrderID = '#URL.OrderID#'

It errors out. How do I INNER JOIN and pull data from 4 tables?

----------------------------------------
Florida Web Design
Orlando Web Hosting
Florida Coldfusion Hosting

 

[r937]

normally when you ask for help with sql, you would need to tell people which database system you're using

however, this can only be microsoft access

you need to parenthesize your joins -- you did it for 3 tables, just keep parenthesizing as you add additional tables

SELECT [i]columns[/i]
  FROM (
       (
       Orders 
INNER 
  JOIN OrderItems 
    ON Orders.OrderID 
     = OrderItems.OrderItemOrderID
       )
INNER 
  JOIN Members 
    ON Members.ID 
     = Orders.OrderMemID
       )
INNER 
  JOIN Products 
    ON Products.ProductID 
     = OrderItemProdID.ID
 WHERE Orders.OrderID = '#URL.OrderID#'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[coldfused]

Thanks Rudy.

I will remember to post the database type next time as well.

----------------------------------------
Florida Web Design
Orlando Web Hosting
Florida Coldfusion Hosting