VTbtGTjR

1MySQL data extraction from 3 tables - joins and max
2SELECT video.id AS video_id, video.owner_id, MAX(video.points), tag.id AS tag_id
3    FROM Videos video, VideoTags videotag, Tags tag
4   WHERE video.id = videotag.video_id
5     AND videotag.tag_id = tag.id
6     AND tag.content LIKE '#%'
7GROUP BY tag.id
8	
9DROP TABLE IF EXISTS `Video`;
10CREATE TABLE `Video` (
11  `id` varchar(24) NOT NULL default '',
12  `owner_id` varchar(24) NOT NULL default '',
13  `points` DOUBLE NOT NULL default 0
14);
15
16DROP TABLE IF EXISTS `Tags`;
17CREATE TABLE `Tags` (
18  `id` int(11) NOT NULL AUTO_INCREMENT,
19  `content` varchar(32) NOT NULL default ''
20PRIMARY KEY (id)
21);
22
23DROP TABLE IF EXISTS `VideoTags`;
24CREATE TABLE `VideoTags` (
25  `video_id` varchar(24) NOT NULL default '',
26  `tag_id` int(11) NOT NULL
27);
28
29INSERT INTO Videos (id,owner_id,points) VALUES ('owner-x-video-a','owner-x', 20);
30INSERT INTO Videos (id,owner_id,points) VALUES ('owner-x-video-b','owner-x', 15);
31INSERT INTO Videos (id,owner_id,points) VALUES ('owner-y-video-k','owner-y', 12);
32INSERT INTO Videos (id,owner_id,points) VALUES ('owner-y-video-l','owner-y', 17);
33INSERT INTO Videos (id,owner_id,points) VALUES ('owner-y-video-m','owner-y', 44);
34
35INSERT INTO Tags (id, content) VALUES (111, '#topic-1');
36INSERT INTO Tags (id, content) VALUES (222, '#topic-2');
37
38INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-x-video-a',111);
39INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-x-video-b',111);
40INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-y-video-k',111);
41INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-y-video-l',222);
42INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-y-video-m',222);
43	
44video_id          owner_id    MAX(video.points)  tag_id
45owner-x-video-a   owner-x     20                 111
46owner-y-video-m   owner-y     44                 222
47	
48video_id          owner_id    MAX(video.points)  tag_id
49owner-x-video-a   owner-x     20                 111
50owner-y-video-l   owner-y     44                 222
51	
52SELECT * FROM Video JOIN (
53
54  SELECT   VideoTags.tag_id, MAX(points) points
55  FROM     Video JOIN VideoTags ON Video.id = VideoTags.video_id
56  GROUP BY VideoTags.tag_id
57
58) t USING (points) JOIN Tags ON t.tag_id = Tags.id
59	
60In your query 
61
62SELECT video.id AS video_id, video.owner_id, MAX(video.points), tag.id AS tag_id
63    FROM Videos video, VideoTags videotag, Tags tag
64   WHERE video.id = videotag.video_id
65     AND videotag.tag_id = tag.id
66     AND tag.content LIKE '#%'
67GROUP BY tag.id
68
69"videotag.tag_id = tag.id" this will have two row matching so it override the 2nd value 
70
71INSERT INTO Tags (id, content) VALUES (222, '#topic-2');
72
73INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-y-video-l',222);
74INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-y-video-m',222);
75
76which show the wrong result.
77
78So if we try it to group by using "videotag.tag_id" than it will show the right result.
79
80SELECT video.id AS video_id, video.owner_id, MAX(video.points), tag.id AS tag_id
81    FROM Videos video, VideoTags videotag, Tags tag
82   WHERE video.id = videotag.video_id
83     AND videotag.tag_id = tag.id
84     AND tag.content LIKE '#%'
85GROUP BY videotag.tag_id