Joining multiple tables

[paul_wilkins]

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
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?

[r937]

you need a subquery to find the max id, then you need to join back to UserCards to get the price, then you need to join to cards

so, no, you're doing it the best way already

[paul_wilkins]

you need a subquery to find the max id, then you need to join back to UserCards to get the price, then you need to join to cards

so, no, you're doing it the best way already

Thanks for the confirmation there.

If you were to come across a similar situation like this, would you have handled the table structure in a different (potentially better) way?

[r937]

depends on scale

if the MAX subquery approach performs adequately, i'd leave it

alternatively, add yet another table, similar in structure to usercards, but which will contain only the current cards, and maintain this table whenever a card is sold

then your query can avoids the subquery altogether

[oddz]

If performance becomes a issue flagging the most recent card will eliminate the need for the subqueries. Maybe not ideal but if performance becomes a issue its something to think about.

Edit: Which is pretty much what r937 said above but with an additional table.

[crmalibu]

If you decide to maintain a separate table, triggers for insert, update, and delete could be a decent way to go about it.