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.
SELECT Card.*, CurrentCards.price, CurrentCards.purchased
LEFT OUTER JOIN (
INNER JOIN (
SELECT max(id) as maxid
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?