fileman (2) [Avatar] Offline
#1
hi, I am new to hibernate and need some help
I have a table called customers (id(primary key), customer name, purchase date, and receipt number(foreign key))
and
another table called purchases (receipt number(primary key), details, amount)
the mapping is done correctly (many to one)
I want to find out the most recent purchases for each customer with the amount shown. So, basically, the result is a list of unique customers, sorted by purchase date, and grouped by customers name, with the purchase amout shown. Is this possible?
How do I do that?
Any help is welcome, a concrete example will be even better
Thanks a ton
thomaswaltair (1) [Avatar] Offline
#2
Re: group by, order by, and join question
Hi

As per the Many to One relationship between Customer and Purchases table leads to be dupicate enteries in the customer Table as per your table structure.

Ex: one customer having multiple receiptNo. So the CustId will be in duplicate stage

I think find the below structure that will help out

Table:Cusotmer

CustId (Primary key)
CustName

Tablesmilieurchases

ReceiptNo(Primary key)
CustId (Foreign key Refer Customer Table)
PurchaseDate
Details
Amount

One to Many ( Onc customer having multiple purchases)
Many to One ( Number of multiple purchases for a customer)

I think it will clear the structure and the relationship.

Warm Regards
Thomas M. Waltair