SQL select all items of an owner from an item-to-owner table
- by kdobrev
I have a table bike_to_owner. I would like to select current items owned by a specific user.
Table structure is CREATE TABLE IF NOT EXISTS `bike_to_owner` (
`bike_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`last_change_date` date NOT NULL,
PRIMARY KEY (`bike_id`,`user_id`,`last_change_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
In the profile page of the user I would like to display all his/her current possessions.
I wrote this statement:
SELECT `bike_id`,`user_id`,max(last_change_date) FROM `bike_to_owner`
WHERE `user_id` = 3 group by `last_change_date`
but i'm not quite sure it works correctly in all cases.
Can you please verify this is correct and if not suggest me something better.
Using php/mysql.
Thanks in advance!
