Help with Query Please 2

[VonFranzken]

I have a e-commerce relational access database.

I have to create a query that uses 3 tables

Orders:
OrderDetails:
Users:

The orders table has a one to many relationship with the ordersdetails tables as such:

orders 'OrderReferenceID'
&
orderdetails 'DetailOrderID'

And the users table has a one to many relationship with the orders table as such:

users 'UserID'
&
orders 'OrdersUserID'

I need to "SEE" all the orders placed by orderreferenceid and user.
Here are the fields I would require for the query.

Orders: OrderDetails:

OrderReferenceID(PK) DetailOrderID(FK)
OrderUserID DetailQuantity
OrderTotal
OrderDate
OrderStatus

Users:
UserID
FullName

Thanks

 

[DotNetGnat]

Try this:

SELECT t1.UserID, t1.FullName, t2.OrderReferenceID, t2.OrderTotal, t3.DetailQuantity FROM Users t1 INNER JOIN Orders t2 ON t1.UserID=t2.OrderReferenceID INNER JOIN OrderDetails t3 ON t2.OrderReferenceID=t3.DetailOrderID
WHERE t2.OrderReferenceID=10

you can change 10 to whatever ID you want...

-DNG

 

[PHV]

A starting point:
SELECT U.UserID, U.FullName, O.OrderReferenceID, O.OrderTotal, O.OrderDate, O.OrderStatus, D.DetailQuantity
FROM (Users U INNER JOIN Orders O ON U.UserID = O.OrderUserID)
INNER JOIN OrderDetails D ON O.OrderReferenceID = D.DetailOrderID
WHERE your criterias here
ORDER BY 2, 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VonFranzken]

Can the where criteria be where the order id lets say = order detials id?

Thanks.

Steve

 

[DotNetGnat]

then you have to say...

WHERE t3.OrderDetailID=10

just reference the correct table...

-DNG

 

[VonFranzken]

by why = 10?

 

[PHV]

I guess you're confused with join clause and where clause.
What is the REAL issue ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DotNetGnat]

you can have anything there...may be a value passed from your form...i was jut giving you an example...

-DNG

 

[jsteph]

VonFranzen,
WHERE and JOIN are really the same--the JET Execution plan will do a JOIN if you use WHERE on those same fields in the JOIN--it's a matter of symantics when writing sql. Oracle never even had a JOIN clause.

But it's easier to read if you use JOIN, and if you use WHERE for the 'join' fields, use parens to isolate it to help JET with the deciding how to process the join.
--Jim

 

[jsteph]

...VonFranzen,
I should clarify..."WHERE and JOIN are really the same" should go on to say "...in the context you presented". You can use WHERE instead of JOIN but not necessarily the other way around of course...