CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
broke
broke

Reputation: 8302

Need help creating a query for a non-normalized database

I've never worked with a non-normalized database before, so I'll try and explain my problem as best I can. So I have two tables:

enter image description here

The customers table holds all the customers information, and the orders table holds all the orders that they have placed. I haven't listed all the fields in the tables, just the ones that I need. The customer number in both tables is not the primary key, but I'm inner joining on them anyway. So the problem I'm having is that I don't know how to make a query that:

Selects all the customers with their first name, last name, and email, and also show the most recent orderdate, most recent total, and most recent ordertype. I know that I have to use a max() aggregate for the date, but that's as far as I got. Please help a noob out.

Upvotes: 0

Views: 152

Answers (4)

MethodMan
MethodMan

Reputation: 18863

If you are doing this with SQL Server use the query designer and basically all you want to do is do a join since you have two keys that are the same one in Customer Table ->Customer Join on Order->Customer alias the Customer table as C and Orders table as O

so for example 

SELECT Customer.*, Orders.* 
From Customer c, Orders O INNER JOIN O where C.Customer Number = O.Customer Number

This should be enough to get you started.. if you don't want all the fields then fully qualify the names for example 

SELECT C.FirstName, C.LastName, O.OrderDate, O.OrderType FROM Customer C, Orders O 
WHERE C.Customer NUmber = O.Customer Number //this is another way of doing a Join when working with the where Clause.

Upvotes: 0

Gixonita
Gixonita

Reputation: 805

You can try:

SELECT FirstName,
       LastName,
       Email,
       OrderDate,
       OrderTotal,
       OrderType

FROM Customers AS C
INNER JOIN Order AS O
  ON O.CustomerNumber = C.CustomerNumber AND
     O.OrderDate = (
                    SELECT MAX (O1.OrderDate)
                    FROM Order AS O1
                    WHERE O1.CustomerNumber = C.CustomerNumber)
                   )

Upvotes: 3

CristiC
CristiC

Reputation: 22708

Try this:

SELECT
 Customers.*, Orders.*
FROM
 Customers
JOIN 
 (SELECT
   Customer_Number,
   MAX(Order_Date) OrderDate
  FROM
   Orders
  GROUP BY 
   Customer_Number
) as Ord ON Customers.Customer_Number = Ord.Customer_Number
JOIN Order ON Orders.Customer_Number = Ord.Customer_Number

Upvotes: 0

dlgrasse
dlgrasse

Reputation: 308

assuming that Orders.OrderDate is unique for each CustomerNumber, does this work for you? if a single CustomerNumber has more than one entry in Order for OrderDate, you'll get each of those rows.

select c.FirstName, c.LastName, c.Email, o.OrderDate, o.OrderTotal, o.OrderType
from Customers c
join
(select CusomterNumber, max(OrderDate) as MostRecentOrderDate
 from Orders
 group by CustomerNumber
) mro on mro.CustomerNumber=s.CustomerNumber
join Orders o on o.OrderDate=mro.MostRecentOrdeDate and
                 o.CustomerNumber=mro.CustomerNumber

Upvotes: 2

Related Questions