Question on Joint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Question on Joint

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-07-30 : 19:42:33

Hi,

I have the following 4 tables:

CREATE TABLE tempCustomer (userID smallint PRIMARY KEY IDENTITY, username nvarchar(50));
INSERT INTO tempCustomer VALUES ('Peter');
INSERT INTO tempCustomer VALUES ('Paul');
INSERT INTO tempCustomer VALUES ('Mary');

CREATE TABLE tempProduct (productID smallint PRIMARY KEY IDENTITY, productDesc nvarchar(50), unitPrice money);
INSERT INTO tempProduct VALUES ('apple', 1.00);
INSERT INTO tempProduct VALUES ('orange', 1.20);
INSERT INTO tempProduct VALUES ('banana', 2.00);

CREATE TABLE tempOrder (orderID smallint PRIMARY KEY IDENTITY, orderDate smalldatetime, userID smallint);
INSERT INTO tempOrder VALUES ('01/01/2006', 2);
INSERT INTO tempOrder VALUES ('01/01/2007', 2);
INSERT INTO tempOrder VALUES ('02/02/2005', 1);

CREATE TABLE tempOrderProduct(orderProductID smallint PRIMARY KEY IDENTITY, orderID smallint, productID smallint, quantity smallint);
INSERT INTO tempOrderProduct VALUES (1, 2, 3);
INSERT INTO tempOrderProduct VALUES (1, 3, 2);
INSERT INTO tempOrderProduct VALUES (1, 1, 1);
INSERT INTO tempOrderProduct VALUES (2, 1, 3);
INSERT INTO tempOrderProduct VALUES (2, 2, 3);
INSERT INTO tempOrderProduct VALUES (3, 2, 6);

Now I would like to write a query that would return the info of the LATEST ONE order for all users. There should be a column for username, orderID, orderDate and total cost of that latest order.

So 2 orders should show up in this query - one for Peter and one (the 2007 one) for Paul.

How can I achieve this?

Thanks,
ywb

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-31 : 05:45:48

... or even JOINs!
Try using derived tables. Something like:

SELECT C.UserName, O.OrderID, O.OrderDate, D2.OrderCost
FROM tempCustomer C
    JOIN (
            SELECT O1.UserID, MAX(O1.OrderDate) AS OrderDate
            FROM tempOrder O1
            GROUP BY O1.UserID
        ) D1
        ON C.UserID = D1.UserID
    JOIN tempOrder O
        ON D1.UserID = O.UserID
            AND D1.OrderDate = O.OrderDate
    JOIN (
            SELECT X1.OrderID
                ,SUM(X1.Quantity * P1.UnitPrice) AS OrderCost
            FROM tempOrderProduct X1
                JOIN tempProduct P1
                    ON X1.ProductID = P1.ProductID
            GROUP BY X1.OrderID
        ) D2
        ON O.OrderID = D2.OrderID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 05:59:28

[code]SELECT d.userName,
d.orderID,
d.orderDate,
d.totalCost
FROM (
SELECT c.userName,
o.orderID,
o.orderDate,
SUM(op.quantity * p.unitPrice) AS totalCost,
ROW_NUMBER() OVER (PARTITION BY o.orderID ORDER BY o.orderDate DESC) AS RecID
FROM tempCustomer AS c
INNER JOIN tempOrder AS o ON o.userID = c.userID
INNER JOIN tempOrderProduct AS op ON op.orderID = o.orderID
INNER JOIN tempProduct AS p ON p.productID = op.productID
GROUP BY c.userName,
o.orderID,
o.orderDate
) AS d
WHERE d.RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2007-07-31 : 14:49:35

select tempOrder.OrderID, tempOrder.orderdate, tempCustomer.username, Sum(tempOrderProduct.quantity * tempProduct.UnitPrice) as Cost
from tempCustomer
inner join tempOrder on tempCustomer.userid = tempOrder.userid
inner join tempOrderProduct on tempOrder.orderid = tempOrderProduct.orderid
inner join tempProduct on tempOrderProduct.productid = tempProduct.productid
where tempOrder.orderdate = (select max(tempOrder.orderdate) from tempOrder where tempOrder.Userid = tempCustomer.userid)
Group by tempOrder.OrderID,tempCustomer.username,tempOrder.orderdate

Kapil Arya

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-08-01 : 15:54:07

Hi,

Thanks guys!

Suggestions from both kapilarya & Ifor achieve exactly what I want.

I get an error "'ROW_NUMBER' is not a recognized function name." with Peso's query though.

Thanks again!

Regards,
ywb

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-01 : 16:16:58

quote:
Originally posted by ywb

I get an error "'ROW_NUMBER' is not a recognized function name." with Peso's query though.

Are you using SQL Server 2005? If you are, what is the compatibility level set to for this database?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources