?
Solved

SQL Statement Hell!

Posted on 2003-03-20
8
Medium Priority
?
134 Views
Last Modified: 2013-12-24
Hi,

This is probably quite easy, but I'm not very good at sql.
I want to be able to display all of the order details on one page.

I want to get OrderDate, CardType, CardNumber, Cardexpmonth, Cardexpyear, Completed from the Order table,

CustFirstName, CustSurname, CustAddress1, CustAddress2, CustPostcode, CustEmail from the Customer table, where the CustID is the same as that in Order,

Model, Price from Products where the Product ID is the same as that in ProductOrder,

And finally the Make, where MakeID is the same as that in Product.

Kinda complicated huh?

Any help would be appreciated.

Thanks
0
Comment
Question by:ceeferbumble
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8174053

select C.CustFirstName,
       C.CustSurname,
       O.OrderDate,
       O.CardType,
       P.Model,
       P.Price,
From  Customer C, Order O, ProductOrder
Where C.CustID = O.CustID
And   P.ProductID = O.ProductID

Now, is there is another table called Make
"And finally the Make, where MakeID is the same as that in Product."


0
 
LVL 5

Accepted Solution

by:
JimV_ATL earned 300 total points
ID: 8174081
You may need to tweak this depending on the actually id names, and table names:

select
o.OrderDate,
o.CardType,
o.CardNumber,
o.Cardexpmonth,
o.Cardexpyear,
o.Completed,
c.CustFirstName,
c.CustSurname,
c.CustAddress1,
c.CustAddress2,
c.CustPostcode,
c.CustEmail,
p.Model,
p.Price,
m.make
from [order] o  -- if your order table is actually called order, you'll need the brackets to escape the SQL Reserved word "order"
join customer c on (o.CustID = c.ID) -- this could also be c.CustID?
join product p on (o.ProductID = p.ID)-- this could also be p.ProductID?
join make m on (p.MakeID = m.ID) -- this could also be m.MakeID?
where o.id = YOURORDERID
0
 

Author Comment

by:ceeferbumble
ID: 8175172
Right then, I tried this....

<cfquery name="Getorder" datasource="bmx">
SELECT
Order.OrderDate,
Order.CardType,
Order.CardNumber,
Order.Cardexpmonth,
Order.Cardexpyear,
Order.Completed,
Customer.CustFirstName,
Customer.CustSurname,
Customer.CustAddress1,
Customer.CustAddress2,
Customer.CustPostcode,
Customer.CustEmail,
Product.Model,
Product.Price,
Make.Makename
FROM[order]
JOIN Customer on ([Order].CustID = Customer.CustID)
JOIN Product on ([Order].ProductID = Product.ProductID)
JOIN Make on (Product.MakeID = Make.MakeID)
WHERE [Order].OrderID = '#FORM.Order#'
</cfquery>

And CF reckons theres a Syntax error in my FROM clause.

Do I have to use the abreiviations like c, p, m etc for the joins to work?

Thanks for your help!
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:ceeferbumble
ID: 8175203
Realised that ProductOrder wasn't there, so I now have...

<cfquery name="Getorder" datasource="bmx">
SELECT
Order.OrderDate,
Order.CardType,
Order.CardNumber,
Order.Cardexpmonth,
Order.Cardexpyear,
Order.Completed,
Customer.CustFirstName,
Customer.CustSurname,
Customer.CustAddress1,
Customer.CustAddress2,
Customer.CustPostcode,
Customer.CustEmail,
Product.Model,
Product.Price,
Make.Makename
FROM[order]
JOIN Customer on ([Order].CustID = Customer.CustID)
JOIN ProductOrder on ([Order].OrderID = ProductOrder.OrderID)
JOIN Product on (ProductOrder.ItemID = Product.ProductID)
JOIN Make on (Product.MakeID = Make.MakeID)
WHERE [Order].OrderID = '#FORM.Order#'
</cfquery>

But I still get the same error!
0
 

Author Comment

by:ceeferbumble
ID: 8175472
Tried this too...

<cfquery name="Getorder" datasource="bmx">
SELECT
Order.OrderDate,
Order.CardType,
Order.CardNumber,
Order.Cardexpmonth,
Order.Cardexpyear,
Order.Completed,
Customer.CustFirstName,
Customer.CustSurname,
Customer.CustAddress1,
Customer.CustAddress2,
Customer.CustPostcode,
Customer.CustEmail,
Product.Model,
Product.Price,
Make.Makename,
ProductOrder.Quantity
FROM [Order], Customer, Product, Make
WHERE [Order].CustID = Customer.CustID
AND [Order].OrderID = ProductOrder.OrderID
AND ProductOrder.ItemID = Product.ProductID
AND Product.MakeID = Make.MakeID
AND [Order].OrderID = '#FORM.Order#'
</cfquery>

But got the error

Too few parameters. Expected 3

As I said, I'm not very good at SQL!
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 8176218
there are a couple errors I see in your last post...

As you have the query you would need to change your selects from the order table from

Order.OrderDate,
Order.CardType,
etc..

to

[Order].OrderDate,
[Order].CardType,
etc..

since order is a reserved word

and also you are missing the table "ProductOrder" In your FROM clause

I would recommend using INNER JOIN similar to what JimV_ATL previously posted.

0
 

Expert Comment

by:Anticlue
ID: 8180079
Hi,

Also, it maybe be good to use a prefix in front of the table names.  To help identify them, and eliminate the possiblity of using reserved words.

Try renaming Order to tblOrder, Product to tblProduct, and so on.

What database are you using?
0
 

Author Comment

by:ceeferbumble
ID: 8186064
Using Access as my database.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month13 days, 4 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question