Relationships in MySQL and QUERY problem

Question

Hi I have two questions about MySQL and QUERIES.

First of all i've like to know what diffrence does it make at forming queries if i setup relationships between the tables in phpmyadmins designer or if i dont?
And second I have 3 tables and have some logical error in my query it seems that MAX() function and GROUP BY do not go well together ://

So if anyone know how to solve my problem, please share :)

And yes I'm using MySQL 5.1.41 and I made the relationships between tables

CREATE TABLE  `sandbox`.`videos` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 255 ) NOT NULL ,
`views` INT NOT NULL ,
`id_user` INT UNSIGNED NOT NULL ,
`id_type` INT UNSIGNED NOT NULL ,
INDEX (  `id_user` ,  `id_type` )
) ENGINE = INNODB;

id title views id_user id_type
------------------------------------
1 video1 50  1  1
2 video2 55  3  1
3 video3 100  2  3
4 video4 20  5  3
5 video5 62  4  5



CREATE TABLE  `sandbox`.`users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL
) ENGINE = INNODB;

id name
-----------
1 adam
2 mike
3 chuck
4 walker
5 nancy



CREATE TABLE  `sandbox`.`types` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL
) ENGINE = INNODB;

id name
-------------
1 20 min
2 30 min
3 50 min
4 90 min
5 120 min

MY QUERY:

SELECT videos.id, videos.title, MAX(videos.views) AS views, users.name AS user, types.name AS type 
FROM videos, users, types 
WHERE videos.id_user = users.id 
AND videos.id_type = types.id 
GROUP BY id_type  
ORDER BY id_type ASC
LIMIT 0 , 20;

QUERY RESULT:

id title views user type
------------------------------------
1 video1 55  adam 20 min
3 video3 100  mike 50 min
5 video5 62  walker 120 min

The GROUP BY function groups all entries by type and takes data only from first entry in the group, but because of MAX() function it copies the highest value from views and writes it to the first entry in the group.

WANTED RESULT:

id title views user type
------------------------------------
1 video2 55  chuck 20 min
3 video3 100  mike 50 min
5 video5 62  walker 120 min

What I need is that it selects all the data (title and username also) not only views data and groups them by type.

Thx for any help :)

i hope this help : http://stackoverflow.com/questions/979034/mysql-shows-incorrect-rows-when-using-group-by — Haim Evgi, Jan 26 '11 at 13:29

score 0 · Answer 1 · answered Jan 26 '11 at 13:28

Try this not a pretty good method but may still suit your requirement

SELECT *,MAX(result.views) AS views FROM(SELECT videos.id, videos.title, users.name AS user, types.name AS type 
    FROM videos, users, types 
    WHERE videos.id_user = users.id 
    AND videos.id_type = types.id ORDER BY videos.views DESC) AS `result` 
    GROUP BY `result`.`id_type`  
    ORDER BY  `result`.id_type ASC
    LIMIT 0 , 20;

score 0 · Accepted Answer · answered Jan 26 '11 at 13:31

First, the aggregate functions such as MIN(), MAX, AVG(), COUNT(), SUM() will all use a group by if there is another column involved, such as your example... It needs to know at what break to consider each group... (with exception of something like SELECT MAX(Balance) from CustomerAccount -- where this gets the maximum balance no matter who or what).

What you need to do is pre-query your condition and then get the elements associated with that qualifier.

SELECT 
      id_type, 
      max( views )
   from
      videos
   group by 
      id_type

Now, this gives me the inner most pre-qualification... per Type of video, what was the maximum regardless of ID. Now, in case there are issues of multiple videos having the same count, I'm also going to use a MIN() qualifier of the ID in a second level query.

select 
      PreQuery.id_type,     
      min( v.ID ) as FirstVideoIDByType
   from
      ( SELECT 
             id_type, 
             max( views )
          from
             videos
          group by 
             id_type ) PreQuery,
      Videos v
   where 
      PreQuery.id_type = v.id_type
   group by 
      PreQuery.id_type

Finally, I'll Now have the first instance of an ID with the maximum views per video type, now, get the details for those specific videos

select 
      FinalVideo.*
   from
      ( select 
             PreQuery.id_type,     
             min( v.ID ) as FirstVideoIDByType
          from
             ( SELECT 
                    id_type, 
                    max( views )
                 from
                    videos
                 group by 
                    id_type ) PreQuery,
             Videos v
          where 
             PreQuery.id_type = v.id_type
          group by 
             PreQuery.id_type ) PreQuery2,
      Videos v2
   where
      PreQuery2.FirstVideoIDByType = v2.id
   order by
      v2.id_type
   limit
      0, 20;

Hope this clarifies how and why things work the way they do... Sometimes complex to break down the elements to get what you really want.

If you wanted to include all videos that might be tied for the same max views count, you could skip the secondary query, but join with the same maximum view count, via

select 
      FinalVideo.*
   from
      ( SELECT 
              id_type, 
              max( views ) MaxViews
           from
              videos
           group by 
              id_type ) PreQuery,
       Videos v
    where 
           PreQuery.id_type = v.id_type
       and PreQuery.MaxViews = v.views
   order by
      v.id_type
   limit
      0, 20;

Relationships in MySQL and QUERY problem

MY QUERY:

QUERY RESULT:

WANTED RESULT:

2 Answers2