I'm being asked to add extra information to an existing database structure that involves a User table and a trading card table called Card.
The extra information that I'm asked to add is the purchase price and date for when the user purchased one of the trading cards, so I figure that a third table is required, called UsersCard
Whenever a card is sold to someone else, a new entry is added to UsersCard. That table is to contain a continuous history of purchase date and price, which means that a user may have several entries for a single card, only the most recent entry being applicable for when viewing the card information.
Collecting the card information has been tricky for me though, and I can't help but think that there is a better way to retrieve the information.
Code sql:SELECT Card.*, CurrentCards.price, CurrentCards.purchased FROM Card LEFT OUTER JOIN ( SELECT UsersCard.* FROM UsersCard INNER JOIN ( SELECT MAX(id) AS maxid FROM UsersCard WHERE UsersCard.userid = %d GROUP BY cardid ) AS MostCurrent ON MostCurrent.maxid = UsersCard.id ) AS CurrentCards ON CurrentCards.cardid = Card.id WHERE Card.userid = %d ORDER BY Card.id
It works, but it seems to be a lot of code just to get a the cards for a user with the most recent UsersCard info for that users cards.
Is there a better way of retrieving this info that I'm just not seeing?