Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

SQL Statement Hell!

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
ceeferbumble
Asked:
ceeferbumble
1 Solution
 
HamdyHassanCommented:

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
 
JimV_ATLCommented:
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
 
ceeferbumbleAuthor Commented:
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
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
ceeferbumbleAuthor Commented:
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
 
ceeferbumbleAuthor Commented:
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
 
danrosenthalCommented:
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
 
AnticlueCommented:
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
 
ceeferbumbleAuthor Commented:
Using Access as my database.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now