make "meny to meny" as "one to meny"

[sako2]

hello,
i know the what i want to do, is the opposite of 3ed noraml Form DB. but this is my case!

i have table costemers. includes "name" column
i have talbe orders. includes "date" and "amount" column
i would like to read costerms who thier name starts with 'm' and i want to read in the same time thier last order date.

in simple model i will get something like this.
---------------------------
nr name date ammount
1 maximi 1.1.2004 20
2 maximi 1.2.2004 4000
3 maximi 1.3.2004 100
4 mand 5.3.2004 1000
5 mand 1.2.2004 28
---------------------------
what i really want to have is :
---------------------------
nr name date ammount
1 maximi 1.3.2004 100
2 mand 5.3.2004 1000
---------------------------
i mean for each user one row including the date of the last order. i have ofcourse the proper PKs and FKs.

can i do that at all in one sql statement?

regards.

sako.

 

[PHV]

The subquery in the WHERE clause approach:
SELECT .... FROM orders o1 INNER JOIN ...
WHERE ...
AND o1.date=(SELECT Max(o2.date) FROM orders o2 WHERE o2.custID=o1.custID)

The join with an aggregate query approach:
SELECT .....
INNER JOIN (SELECT custID, Max(orders.date) AS MaxDate FROM orders GROUP BY custID) M ON o1.custID=M.custID AND o1.date=M.MaxDate
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244