The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

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