Multiple tables and multiple joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple tables and multiple joins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-03 : 08:22:44

Chris writes "What are the rules that orchestrate a multiple table/join statement? I have written the below code and get error messages telling me that "The column prefix 'customer_orders_prod' does not match with a table name or alias name used in the query." I'm obviously breaking some fundamental rule/s but can't find or figure out what that might be. Your assistance and explanation of my error would be greatly appreciated.

SELECT customer_orders.id,
customer_orders.orderdate,
customer_orders.customer_id as coid,
customer_orders.contact_id,
customer_log.logdate,
customer_log.customer_id as clid,
customer_log.notes,
customer_log.logtype,
customer.business,
customer.category,
customer_orders_prod.order_id,
customer_orders_prod.product_id,
customer_orders_prod.qty,
customer_orders_prod.line_total,
inventory.service_name
FROM customer_log
LEFT OUTER JOIN
inventory on customer_orders_prod.product_id = inventory.service_id
JOIN
customer_orders_prod on customer_orders.id = customer_orders_prod.order_id
JOIN
customer_orders on customer_log.notes = customer_orders.id
JOIN
customer on customer_log.customer_id = customer.id
WHERE
customer_log.logdate BETWEEN #start# AND #end#
<cfif not form.product_id EQ 0>
AND
customer_orders_prod.product_id = #form.product_id#
</cfif>
ORDER BY customer_log.logdate ASC"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-09-03 : 09:38:35

quote:

FROM customer_log
LEFT OUTER JOIN
inventory on customer_orders_prod.product_id = inventory.service_id

here's the source of the parsing error. you have two rowsets referenced above, customer_log and inventory, but your ON clause references a third rowset, customer_orders_prod.

when you join X together with Y, the only variables you can reference in the ON clause are columns of X, columns of Y, or scalar values.

Jonathan
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources