Inner Join syntax

[lilbuckeye]

I am trying to add another table to this query. tbl_orderskus
Can anyone help me with this inner join syntax in adding the new table
below?
How can I modify to get this to work correctly with the three table?

<!--- retrieve orders by date --->
<cfquery name="rsByDate" datasource="nsstore2.mdb"
username="#request.dsnUsername#" password="#request.dsnPassword#">
SELECT tbl_customers.cst_FirstName, tbl_customers.cst_LastName,
tbl_customers.cst_Zip,
tbl_orders.order_ID, tbl_orders.order_Date, tbl_customers.cst_Address1,
tbl_customers.cst_Address2, tbl_customers.cst_City, tbl_orders.order_State,
tbl_customers.cst_Phone, tbl_customers.cst_email, tbl_customers.cst_ShpName,
tbl_customers.cst_ShpAddress1, tbl_customers.cst_ShpAddress2,
tbl_customers.cst_ShpCity,
tbl_customers.cst_ShpZip,
tbl_orders.order_ShipMeth_ID,
tbl_customers.cst_Phone,
tbl_orders.order_TransactionID,
tbl_orders.order_SKU_ID,
tbl_orders.orderSKU_OrderID,
tbl_orderskus.orderSKU_SKU,
tbl_orderskus.orderSKU_Quantity


FROM tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID AND tbl_orderskus.ordersku_orderID
WHERE tbl_orders.order_Date >=
#CreateODBCDate(LSParseDateTime(FORM.StartDate))#
AND tbl_orders.order_Date <=
#CreateODBCDateTime(DateAdd("d",1,LSParseDateTime( FORM.EndDate)))#
<cfif FORM.Status NEQ 0>AND order_Status = #FORM.Status#</cfif>
ORDER BY tbl_orders.order_Date DESC
</cfquery>

[mxstu]

The exact syntax depends on the database you are using. What column in the
"tbl_orderskus" table corresponds to the other table(s). The basic syntax is
something like the example below.

--- Note - Access db's usually require parenthesis for tables in from clause
....
FROM tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID
INNER JOIN tbl_orderskus ON tbl_orderskus.ordersku_orderID =
someTable.someColumnHere

[lilbuckeye]

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

Can you tell me if there is a certain place I need to add these parenthesis
or Do you think something else is wrong with the From clause?



<!--- retrieve orders by date --->
<cfquery name="rsByDate" datasource="nsstore2.mdb"
username="#request.dsnUsername#" password="#request.dsnPassword#">
SELECT tbl_customers.cst_FirstName, tbl_customers.cst_LastName,
tbl_customers.cst_Zip,
tbl_orders.order_ID, tbl_orders.order_Date, tbl_customers.cst_Address1,
tbl_customers.cst_Address2, tbl_customers.cst_City, tbl_orders.order_State,
tbl_customers.cst_Phone, tbl_customers.cst_email, tbl_customers.cst_ShpName,
tbl_customers.cst_ShpAddress1, tbl_customers.cst_ShpAddress2,
tbl_customers.cst_ShpCity,
tbl_customers.cst_ShpZip,
tbl_orders.order_ShipMeth_ID,
tbl_customers.cst_Phone,
tbl_orders.order_TransactionID,
tbl_orderskus.orderSKU_ID,
tbl_orderskus.orderSKU_OrderID,
tbl_orderskus.orderSKU_SKU,
tbl_orderskus.orderSKU_Quantity



FROM (tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID)(INNER JOIN tbl_orders ON tbl_orders.order_ ID =
tbl_orderskus.ordersku_OrderID)

WHERE tbl_orders.order_Date >=
#CreateODBCDate(LSParseDateTime(FORM.StartDate))#
AND tbl_orders.order_Date <=
#CreateODBCDateTime(DateAdd("d",1,LSParseDateTime( FORM.EndDate)))#
<cfif FORM.Status NEQ 0>AND order_Status = #FORM.Status#</cfif>
ORDER BY tbl_orders.order_Date DESC
</cfquery>

[mxstu]

I think Access uses parenthesis to indicate precedence, like this:

FROM ( (Do this join first) Do this join second )

... so try something like this...

FROM ((tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orders ON tbl_orders.order_ ID =
tbl_orderskus.ordersku_OrderID)

[lilbuckeye]

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression 'tbl_orders.order_ ID =
tbl_orderskus.ordersku_OrderID'.

Still not working....do you have any other suggestion? or Can you tell me
where I can learn more about these join syntax's?
Thank you.

<!--- retrieve orders by date --->
<cfquery name="rsByDate" datasource="nsstore2.mdb"
username="#request.dsnUsername#" password="#request.dsnPassword#">
SELECT tbl_customers.cst_FirstName, tbl_customers.cst_LastName,
tbl_customers.cst_Zip,
tbl_orders.order_ID, tbl_orders.order_Date, tbl_customers.cst_Address1,
tbl_customers.cst_Address2, tbl_customers.cst_City, tbl_orders.order_State,
tbl_customers.cst_Phone, tbl_customers.cst_email, tbl_customers.cst_ShpName,
tbl_customers.cst_ShpAddress1, tbl_customers.cst_ShpAddress2,
tbl_customers.cst_ShpCity,
tbl_customers.cst_ShpZip,
tbl_orders.order_ShipMeth_ID,
tbl_customers.cst_Phone,
tbl_orders.order_TransactionID,
tbl_orderskus.orderSKU_ID,
tbl_orderskus.orderSKU_OrderID,
tbl_orderskus.orderSKU_SKU,
tbl_orderskus.orderSKU_Quantity



FROM ((tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orders ON tbl_orders.order_ ID =
tbl_orderskus.ordersku_OrderID)

WHERE tbl_orders.order_Date >=
#CreateODBCDate(LSParseDateTime(FORM.StartDate))#
AND tbl_orders.order_Date <=
#CreateODBCDateTime(DateAdd("d",1,LSParseDateTime( FORM.EndDate)))#
<cfif FORM.Status NEQ 0>AND order_Status = #FORM.Status#</cfif>
ORDER BY tbl_orders.order_Date DESC
</cfquery>

[CF_Oracle]

Just remove second set of parentesies in FROM section and make it like this
FROM (tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orders ON tbl_orders.order_ ID =
tbl_orderskus.ordersku_OrderID

[lilbuckeye]

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression 'tbl_orders.order_ ID =
tbl_orderskus.ordersku_OrderID'.

The error occurred. on line 71.
Nope, sorry still not working.
It says the error is on line 71 maybe it is something else that is wrong but
it works great without the new addition of the tblskus.
????

<!--- retrieve orders by date --->
<cfquery name="rsByDate" datasource="nsstore2.mdb"
username="#request.dsnUsername#" password="#request.dsnPassword#">
SELECT tbl_customers.cst_FirstName, tbl_customers.cst_LastName,
tbl_customers.cst_Zip,
tbl_orders.order_ID, tbl_orders.order_Date, tbl_customers.cst_Address1,
tbl_customers.cst_Address2, tbl_customers.cst_City, tbl_orders.order_State,
tbl_customers.cst_Phone, tbl_customers.cst_email, tbl_customers.cst_ShpName,
tbl_customers.cst_ShpAddress1, tbl_customers.cst_ShpAddress2,
tbl_customers.cst_ShpCity,
tbl_customers.cst_ShpZip,
tbl_orders.order_ShipMeth_ID,
tbl_customers.cst_Phone,
tbl_orders.order_TransactionID,
tbl_orderskus.orderSKU_ID,
tbl_orderskus.orderSKU_OrderID,
tbl_orderskus.orderSKU_SKU,
tbl_orderskus.orderSKU_Quantity



FROM (tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orders ON tbl_orders.order_ ID =
tbl_orderskus.ordersku_OrderID

WHERE tbl_orders.order_Date >=
#CreateODBCDate(LSParseDateTime(FORM.StartDate))#
AND tbl_orders.order_Date <=
#CreateODBCDateTime(DateAdd("d",1,LSParseDateTime( FORM.EndDate)))#
Line 71 <cfif FORM.Status NEQ 0>AND order_Status = #FORM.Status#</cfif>
ORDER BY tbl_orders.order_Date DESC
</cfquery>

[mxstu]

>Can you tell me where I can learn more about these join syntax's?

Access documentation / help files ;-)

[mxstu]

lilbuckeye,

My mistake. A cut and paste job gone awry. It should be:

FROM (tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orderskus ON tbl_orders.order_Id =
tbl_orderskus.ordersku_OrderID

... instead of ...


FROM (tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orders ON tbl_orders.order_ ID =
tbl_orderskus.ordersku_OrderID

[lilbuckeye]

:D

You are so Awesome!
Thank you so much, it is working!

[lilbuckeye]

I can't seem to figure out these inner join syntax -
I want to add :

INNER JOIN tbl_skus ON tbl_skus.sku_Id = tbl_orderskus.ordersku_sku

to the following:

FROM (tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orderskus ON tbl_orders.order_Id =
tbl_orderskus.ordersku_OrderID

and I am getting this error:
Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression 'tbl_orders.order_Id =
tbl_orderskus.ordersku_OrderID INNER JOIN tbl_skus ON tbl_skus.sku_Id =
tbl_orderskus.ordersku_sku'.

Below is how I have it written: Can anyone please help in adding the new inner
join without getting the syntax error?

FROM (tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orderskus ON tbl_orders.order_Id =
tbl_orderskus.ordersku_OrderID INNER JOIN tbl_skus ON tbl_skus.sku_Id =
tbl_orderskus.ordersku_sku

[Rick]

It looks like you are using Access why don't you just let Access build it
for you. In the query design view just drag the field you want to join to
the other table / field and then right click on the link and you can adjust
it from there.


"lilbuckeye" <webforumsuser@macromedia.com> wrote in message
news:draha3$6i8$1@forums.macromedia.com...

>I can't seem to figure out these inner join syntax -
> I want to add :
>
> INNER JOIN tbl_skus ON tbl_skus.sku_Id = tbl_orderskus.ordersku_sku
>
> to the following:
>
> FROM (tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
> tbl_orders.order_CustomerID) INNER JOIN tbl_orderskus ON
> tbl_orders.order_Id =
> tbl_orderskus.ordersku_OrderID
>
> and I am getting this error:
> Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC
> Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> operator) in query expression 'tbl_orders.order_Id =
> tbl_orderskus.ordersku_OrderID INNER JOIN tbl_skus ON tbl_skus.sku_Id =
> tbl_orderskus.ordersku_sku'.
>
> Below is how I have it written: Can anyone please help in adding the new
> inner
> join without getting the syntax error?
>
> FROM (tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
> tbl_orders.order_CustomerID) INNER JOIN tbl_orderskus ON
> tbl_orders.order_Id =
> tbl_orderskus.ordersku_OrderID INNER JOIN tbl_skus ON tbl_skus.sku_Id =
> tbl_orderskus.ordersku_sku
>

[paross1]

Access is rather particular about the placement of the parentheses in these
JOIN statements, so you should try to play around with different combinations
until you arrive at one that works. As a last resort, since you are doing all
inner joins, you might try the pre-ANSI 92 SQL syntax below where you "join"
the tables in the WHERE clause. At least this will tell you if your query is
returning the desired results.

FROM tbl_customers, tbl_orders, tbl_orderskus, tbl_skus
WHERE tbl_customers.cst_ID = tbl_orders.order_CustomerID
AND tbl_orders.order_Id = tbl_orderskus.ordersku_OrderID
AND tbl_skus.sku_Id = tbl_orderskus.ordersku_sku

Phil

[lilbuckeye]

Thanks to all for your responses!

I found someone who knows Access much better than I do and I think we have it
sorted out.
FROM ((tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orderskus ON tbl_orders.order_ID =
tbl_orderskus.orderSKU_OrderID) INNER JOIN tbl_skus ON
tbl_orderskus.orderSKU_SKU = tbl_skus.SKU_ID

Thanks Again!:D

[JMGibson3]

I used to do it in Access and Copy/Paste back into CF until I learned the
trick, now i pretty much do them on the fly.

When adding another Join add another set of parenthesis totally surrounding
what's already there (at the top).

FROM ((tbl_customers INNER JOIN tbl_orders ON tbl_customers.cst_ID =
tbl_orders.order_CustomerID) INNER JOIN tbl_orderskus ON tbl_orders.order_Id =
tbl_orderskus.ordersku_OrderID)
INNER JOIN tbl_skus ON tbl_skus.sku_Id = tbl_orderskus.ordersku_sku

[lilbuckeye]

Now that I can understand how to do.
Thanks so much for posting and sharing.
The next time I need to do this I will know what to do.
:-)