Relationships in MySQL and QUERY problem

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 :)

-------------Problems Reply------------

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;

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;

Category:mysql Views:0 Time:2011-01-26

Related post

  • mysql select query problem 2010-04-08

    i have a form that has a multiple select drop down. a user can select more than one options in the select. the name of the select is array[]; using php I call implode(",",$array) in mysql db, it stores the field as a text in this format "places"= "ne

  • MySQL Multitable Query Problem 2009-04-08

    I have a problem querying multiple tables in MySQL and am currently at my wits end. I have unique IDs in each table, and am using an INNER JOIN to combine them; I am quite new to SQL and this may be the wrong method, hence me posting here: Query: SEL

  • Mysql wildcard query problem 2011-01-11

    WHERE `time` BETWEEN 'Jan 1 %' AND 'Jan 8 %' AND ... Results Jan 1 00:33:23 Jan 10 08:52:05 How would I avoid the Jan 10 results? I have tried a few different combination with %, <=, etc. Any ideas? Thanks --------------Solutions------------- WHER

  • C# MySQL Parameterized Query Problem 2009-05-07

    I have the following method which is used to populate a DAO from the database. It performs 3 reads - one for the primary object, and 2 for some translations. public bool read(string id, MySqlConnection c) { MySqlCommand m = new MySqlCommand(readComma

  • MySQL Database Query Problem 2010-06-02

    I need your help!!!. I need to query a table in my database that has record of goods sold. I want the query to detect a particular product and also calculate the quantity sold. The product are 300 now, but it would increase in the future. Below is a

  • Annoying mysql update query problem 2010-11-09

    I'm trying to update using mysql_query in php and it gives me this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read='1' WHERE id='14'' at line 1 I've be

  • Mysql match query problem 2010-11-22

    Natural Language Full-Text Searches I use the mysql Match function. What's wrong with it? $ititle = wp_title('',0,''); $query = mysql_query("SELECT posttitle, posturl, siteurl MATCH (posttitle, posturl, siteurl) AGAINST $ititle AS score FROM interlin

  • mysql join query problem 2010-11-24

    I have 2 tables 1) "products" with fields (productid PK, name, description, price) 2) "sales" with fields (salesid PK, salestime, productid, customername, customeremail, status) I need to display data in table format as SalesID Product Name Amount Cu

  • MySql Insert Query Problem 2011-02-13

    Whats wrong with insert query. $result = $conn->query("insert into user (fullname,username,email,passwd) values ('".$data['fullname']."','".$data['username']."','".$data['email']."','".$data['fullname']."') " ); if(!$result) { throw new Exception(

  • mysql multistatement query problem(if one query fail how we rollback) 2011-03-16

    I am using Mysql c api for database interaction, I want to know that if one query of Mysql multi statement fail then how we roll back all query which successfully executed.. ex- mysql_query(conn,"select * from account_db; select count(*) from account

  • MySQL complex query problem on a single table 2011-08-10

    Having tried several possibilities I have come to a stop. Without overloading the mysql server with hundreds of queries, this is what I am trying to achieve: Here is the table CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `firstname` v

  • mysql IN query problem 2011-08-12

    select docid from A where docid IN ( select distinct(docid) from B) When I execute above query in mysql it takes 33 seconds, which is too long as per the size of data. Below is the details of both tables. Table A : | docid | int(11) | NO | PRI | NULL

  • PHP / MySQL update query problems 2011-12-09

    I'm having trouble making this query work: if(isset($_POST['updateRecord'])) { mysql_query("UPDATE files SET title = '$_POST[title]', description = '$_POST[description]', internal = '$_POST[accessFile]', category = '$_POST[category]' WHERE title = '$

  • Newbie Question: PDO and MYSQL INSERT Query problem 2011-06-15

    I'm attempting to be more secure and start using PDO and prepared statements. This had been recommended to me and I've read up on these two websites: http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/ and http

  • optimize mysql select query on many to one 2011-08-16

    I am trying to optimize a mysql select query but I just can't work out how to do it. I have been reading around here and any other relevant results on google but can't find anybody with quite my problem, hopefully this isn't because I have constructe

  • MySQL slow query log - how slow is slow? 2008-09-18

    What do you find is the optimal setting for mysql slow query log parameter, and why? --------------Solutions------------- I recommend these three lines log_slow_queries set-variable = long_query_time=1 log-queries-not-using-indexes The first and seco

  • MySQL Inner Query with a Where and Group by conditions in cakephp 2009-10-12

    i am having a table named Reports with id report_id user_id 1 1 5 2 1 5 3 1 5 4 2 5 5 2 5 6 3 6 7 3 6 8 4 1 9 4 1 10 4 1 i am trying to write a Query such that user_id = 5 and to find how many reports he has created.(Answer should be of 2 ) i have a

  • MySQL: Data query from multiple tables with views 2010-05-21

    I want to create a query result page for a simple search, and i don't know , should i use views in my db, would it be better if i would write a query into my code with the same syntax like i would create my view. What is the better solution for mergi

  • Optimizing MySQL update query 2011-01-14

    This is currently my MySQL UPDATE query, which is called from program written in Java: String query = "UPDATE maxday SET DatePressureREL = (SELECT " + "Date FROM ws3600 WHERE PressureREL = (SELECT MAX" + "(PressureREL) FROM ws3600 WHERE Date >= '"

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.102 (s). 11 q(s)