Extreme Noob Help needed!!!

hi ,
was wondering if anyone could help me with a few novice SQL questions.
Simply, this is my prob:

Using JOINS, I need to display 3 columns, cust_name, order_num and TOTAL order cost (need to run a SUM command here I think)(all column items come from separate tables)

also, same as above except I need to display one order per customer and order shown should be the highest numbered order.

any help would be greatly appreciated thanks!

can you post the tables and their fields w/a little description of each?...we'll go from there.

Hi,
Forgot to include that info, here it is:

Table 1 - Customers:
`cust_id`, `cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country

Table 2 - Orderitems:
`order_num`, `order_item`, `prod_id`, `quantity`, `item_price`

Table 3 - Orders:
`order_num`, `order_date`, `cust_id` FROM `Orders`

Table 4 - Products:
`prod_id`, `vend_id`, `prod_name`, `prod_price`, `prod_desc`

Table 5 - Vendors:
`vend_id`, `vend_name`, `vend_address`, `vend_city`, `vend_state`, `vend_zip`, `vend_country

So far this is what I think is what I should be doing:
(Original question -
Using joins, display three columns: Customer Name, Order Number, and Total Order Cost.)

SELECT cust_name, order_num, SUM(item_price*quantity) AS Total
FROM OrderItems, Customers, Orders
WHERE Orders.order_num = OrderItems.order_num
GROUP BY cust_name

of course this doesn't seem to work....
thanks in advance!

First of you need to specify from which table you are retrieving "order_num" value from, and also you need all values you are using except the sum inside the Group By, and as well you need another Where condition to connect the 3rd table... you can use a join but it is not necessary

<b>i.e. </b>

Select cust_name, Orders.order_num, SUM(item_price*quantity) AS Total
FROM OrderItems, Customers, Orders
WHERE (Orders.order_num = OrderItems.order_num) AND (Orders.cust_id = Customers.cust_id)
GROUP BY cust_name, Orders.order_num

hope that helps,
Gerbill

Thanks Gerbill!!!

very much appreciated,
last thing if you could.

How would I go about and only display one order per customer, displaying the highest-numbered Order Number (per cust)?

Could I use the DISTINCT and NULL commands for this query?

thanks again!

You mean that you want the "order_num" to be sorted descending (largest # first) but you only want the first one ...the largest one

Yeah it is possible ...I do believe good use of the DISTINCT would make a difference here.

You can select a distinct column then use...

Order By "Name of distinct column" DESC

i.e. Order By Orders.order_num DESC

"the highest-numbered Order Number (per cust)" is easily produced with a subquery

but centricle, what are those backticks doing around your column names? this is the microsoft sql server forum, and those backticks are only valid in mysql

if i gave you a subquery solution, will you be able to run it?

the backticks are just the way the data was pulled off the DB (was only for display purposes).

And yes it is MS SQL, and I could run your subquery (hopefully )

the backticks are just the way the data was pulled off a mysql db

this sounds suspiciously like someone's homework assignment

if you hand this in, you must be prepared to explain the use of the second LEFT OUTER

Hi r937,
well it's not exactly homework its more like questions I compiled myself for my own practise purposes for a new assignment at work.
I'm sorry for the shadiness in my description but this is my 1st week learning SQL.

Since I don't have access to the actual live DB I am practicing with a local test DB (using a SQL explorer/test utility called QTADO - thats where those "ticks" come from).
The actual DB loaded is an ACCESS file but the final server is MS SQL / ORACLE.

I will try the above and let you know, thanks again for the help.

you meant this one? http://gpoulose.home.att.net/

okay, thanks

see, ya learn sumpin every day on this goldurned interweb

yes

Hi r937,
I managed to get my query to run (since your suggestion didn't execute properly on my setup but opened up many ideas) - my query resulted in this:

SELECT C.cust_name, O.order_num,
sum(OI.item_price*OI.quantity)AS Total_Order
FROM Customers AS C, Orders AS O, Orderitems AS OI
WHERE C.cust_id = O.cust_id
AND O.order_num = OI.order_num
AND O.order_num = ( select max(order_num)
FROM Orders
WHERE cust_id = C.cust_id )
GROUP BY C.cust_name, O.order_num


Thanks again for the help!

Till the next problem!