[SOLVED] 2 simple queries into a complex one

[XRayden]

Hi,

I've got 2 queries that i need only one of...

 

i could do the math in PHP, but I KNOW MySQL can do it faster and better...

 

some infos :

-- Generation Time: Dec 27, 2007 at 05:27 AM

-- Server version: 4.1.20

-- PHP Version: 5.0.4

--

-- Database: `BDBTBS`

 

so this is my problem :

 

I Have 3 tables

 

a User one :

 

CREATE TABLE `BTBS_Membres` (
  `ID` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Primary Key (Clé Primaire)',
  `Login` varchar(32) NOT NULL default '' COMMENT 'Username (Nom usager)',
  `Password` varchar(32) NOT NULL default '' COMMENT 'md5 password hash (Mot de passe encrypter)',
  `Prenom` varchar(150) NOT NULL default '' COMMENT 'First Name (Prénom)',
  `Nom` varchar(150) NOT NULL default '' COMMENT 'Last Name (Nom de famille)',
  `Gender` enum('M','F') NOT NULL default 'M' COMMENT 'Gender Type (Sexe)',
  `Courriel` varchar(255) NOT NULL default '' COMMENT 'e-mail address (Courriel)',
  `DDN` date NOT NULL default '0000-00-00' COMMENT 'Birthdate (Date De Naissance)',
  `LocalID` int(11) NOT NULL default '0' COMMENT 'ID of local voting (Lieu de vote)',
  `CodePostal` varchar(10) NOT NULL default '' COMMENT 'Postal Code / Zip (Code Postal)',
  `MobilePhone` varchar(25) default NULL COMMENT 'Mobile Phone Number (Téléphone cellulaire)',
  `PrefG1` int(11) NOT NULL default '0' COMMENT 'Musical Preference #1',
  `PrefG2` int(11) default NULL COMMENT 'Musical Preference #1',
  `PrefG3` int(11) default NULL COMMENT 'Musical Preference #1',
  `Comment` text COMMENT 'Comments or infos (Commentaires ou informations)',
  `Type` enum('Membre','Validation','Bannie') NOT NULL default 'Membre' COMMENT 'Member Type (Membre = active, Validation = in validation process, Bannie = banned) (Type de membre)',
  `Admin` tinyint(1) NOT NULL default '0' COMMENT 'is a user Administrator? (Admin ou non)',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

 

a video file one :

 

CREATE TABLE `BTBS_Videos` (
  `ID` int(10) unsigned NOT NULL auto_increment COMMENT 'Primary Key (Clé Primaire)',
  `MID` int(11) NOT NULL default '0' COMMENT 'Member ID (ID du membre)',
  `State` enum('Uploaded','Approoved','Online','Refused') NOT NULL default 'Uploaded' COMMENT 'State of file (État du vidéo)',
  `Fichier` text NOT NULL COMMENT 'Filename (nom du fichier)',
  `Description` text NOT NULL,
  `Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

 

and a activities one :

 

CREATE TABLE `BTBS_Activites` (
  `ID` int(10) unsigned NOT NULL auto_increment COMMENT 'Primary Key (Clé Primaire)',
  `MID` int(10) unsigned NOT NULL default '0' COMMENT 'Member ID (ID du membre)',
  `VID` int(11) NOT NULL default '0' COMMENT 'video id',
  `Action` enum('Lu','Vote') NOT NULL default 'Lu' COMMENT 'Action type : Lu = this action mark a viewed video, Vote = this action mark a voted result',
  `Result` int(11) NOT NULL default '0' COMMENT 'If voted on, this is the result',
  `infos` text NOT NULL COMMENT 'Other information (IP + header)',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

 

this is a simple case of videos upload and vote, now i need to do is search and fine the best top 5 voted on video in AGE categories!

 

what i've done so far :

 

i can find the total top five of all videos :

 

SELECT `BTBS_Activites`.`VID`, round(sum(`BTBS_Activites`.`Result`)/count(`BTBS_Activites`.`ID`)) as moyenneVote FROM `BTBS_Activites`,`BTBS_Videos` WHERE `BTBS_Activites`.`Action` = 'Vote' AND `BTBS_Videos`.`State` = 'Online' AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID` GROUP BY `BTBS_Activites`.`VID` ORDER BY moyenneVote DESC;

 

and i can do a age search on users :

SELECT `Login` , (YEAR( CURDATE( ) )-YEAR(`DDN`)) - (RIGHT(CURDATE(),5)<RIGHT(`DDN`,5)) AS `age` FROM `BTBS_Membres` WHERE (YEAR( CURDATE( ) )-YEAR(`DDN`)) - (RIGHT(CURDATE(),5)<RIGHT(`DDN`,5)) > '19';

 

how do you think i should proceed to look for the top 5 videos uploaded by ppls aving an age over 19 ??? do you think it's even possible in one mysql statement ?

[fenway]

There's an AVG() function... but you need to join these queries.

[XRayden]

so ok, i've ffound that this :

SELECT `BTBS_Activites`.`VID`, AVG(`BTBS_Activites`.`Result`) as moyenneVote FROM `BTBS_Activites`,`BTBS_Videos` WHERE `BTBS_Activites`.`Action` = 'Vote' AND `BTBS_Videos`.`State` = 'Online' AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID` GROUP BY `BTBS_Activites`.`VID` ORDER BY moyenneVote DESC;

 

worrk exactly the same as thee first one, only ~50 times faster... (from 0.0234 to 0.0006)

 

so my quest is for the unique one...

 

i've tried :

 

SELECT `BTBS_Activites`.`VID` , AVG( `BTBS_Activites`.`Result` ) AS moyenneVote

FROM `BTBS_Activites` , `BTBS_Videos` , `BTBS_Membres`

WHERE `BTBS_Activites`.`Action` = 'Vote'

AND `BTBS_Videos`.`State` = 'Online'

AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID`

AND (

YEAR( CURDATE( ) ) - YEAR( `BTBS_Membres`.`DDN` )

) - ( RIGHT( CURDATE( ) , 5 ) < RIGHT( `BTBS_Membres`.`DDN` , 5 ) ) >19

AND `BTBS_Membres`.`ID` = `BTBS_Videos`.`ID`

GROUP BY `BTBS_Activites`.`VID`

ORDER BY moyenneVote DESC

 

but it dont work as well, it seam it will output only one video per user, not all videos from all user(may have more than one videos) witch the user himselff is over 19.. seam pretty to dawm hard for my taste... and talent

[fenway]

Post the output of the first query... and what you'd like the second query to display. 

[XRayden]

the tested query give me somthing like this :

+-----+-------------+

| VID | moyenneVote |

+-----+-------------+

|  3 |      4.0000 |

+-----+-------------+

 

what i need is more like this :

 

+-----+-------------+

| VID | moyenneVote |

+-----+-------------+

|  2 |      5.0000 |

|  3 |      4.0000 |

|  4 |      3.0000 |

+-----+-------------+

[fenway]

How are you just getting a single VID from a GROUP BY?

[XRayden]

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[fenway]

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

From which query?

 

What does this produce( from above):

SELECT `BTBS_Activites`.`VID`, AVG(`BTBS_Activites`.`Result`) as moyenneVote FROM `BTBS_Activites`,`BTBS_Videos` WHERE `BTBS_Activites`.`Action` = 'Vote' AND `BTBS_Videos`.`State` = 'Online' AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID` GROUP BY `BTBS_Activites`.`VID` ORDER BY moyenneVote DESC

[XRayden]

this query :

SELECT `Login` , ( YEAR( CURDATE( ) )-YEAR(`DDN`) ) - ( RIGHT(CURDATE(),5 ) < RIGHT(`DDN`,5) ) AS `age` 
FROM `BTBS_Membres` 
WHERE ( YEAR( CURDATE( ) )-YEAR(`DDN`) ) - ( RIGHT(CURDATE(),5) < RIGHT(`DDN`,5) ) > '19';

 

give me somthing like this :

+---------------+------+
| Login         | age  |
+---------------+------+
| olivier.labbe |   30 |
| sitajul       |   57 |
| jjbrig        |   51 |
| jj            |   51 |
| aaa           |   51 |
| qqq           |   51 |
| ccc           |   51 |
| 123           |   51 |
| Lilvero       |   26 |
+---------------+------+

 

and this one :

SELECT `BTBS_Activites`.`VID` , AVG( `BTBS_Activites`.`Result` ) AS moyenneVote
FROM `BTBS_Activites` , `BTBS_Videos`
WHERE `BTBS_Activites`.`Action` = 'Vote'
AND `BTBS_Videos`.`State` = 'Online'
AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID`
GROUP BY `BTBS_Activites`.`VID`
ORDER BY moyenneVote DESC 
LIMIT 5;

 

give me :

+-----+-------------+
| VID | moyenneVote |
+-----+-------------+
|   2 |      5.0000 |
|   3 |      4.0000 |
|   4 |      3.0000 |
+-----+-------------+

 

Activities look like this

mysql> SELECT * FROM `BTBS_Activites` LIMIT 10;
+----+-----+-----+--------+--------+-------+
| ID | MID | VID | Action | Result | infos |
+----+-----+-----+--------+--------+-------+
|  1 |   1 |   2 | Lu     |      0 |       |
|  2 |   1 |   2 | Vote   |      5 |       |
|  3 |   1 |   3 | Lu     |      0 |       |
|  4 |   1 |   3 | Vote   |      4 |       |
|  5 |   1 |   4 | Lu     |      0 |       |
|  6 |   1 |   4 | Vote   |      3 |       |
|  7 |   1 |   5 | Lu     |      0 |       |
|  8 |   1 |   5 | Vote   |      1 |       |
|  9 |   3 |   5 | Lu     |      0 |       |
| 10 |   3 |   5 | Vote   |      4 |       |
+----+-----+-----+--------+--------+-------+

 

videos look like this :

mysql> SELECT * FROM `BTBS_Videos` LIMIT 3;
+----+-----+--------+---------------------------------------------------------------+--------------------+------------+
| ID | MID | State  | Fichier                                                       | Description        | Date       |
+----+-----+--------+---------------------------------------------------------------+--------------------+------------+
|  2 |   1 | Online | olivier.labbe_c2afd6b89d5e961d4e2a62c81ffdb0f9_OLL_000001.mp4 | PublicationWeb.com | 2007-12-21 |
|  3 |   2 | Online | jj.brigaudet_d972541fcfa0464de682e4d8478a2397_JJB_000001.mp4  | Casse-cou          | 2007-12-21 |
|  4 |   3 | Online | sitajul_8b19f2ce2620c5adc8a00c62c27ba231_JJB_000002.mp4       | yasse-you #U       | 2007-12-21 |
+----+-----+--------+---------------------------------------------------------------+--------------------+------------+

 

and users (membres) :

mysql> SELECT `ID`, `Login`, `DDN` FROM `BTBS_Membres` LIMIT 5;
+----+---------------+------------+
| ID | Login         | DDN        |
+----+---------------+------------+
|  1 | olivier.labbe | 1977-11-26 |
|  2 | jj.brigaudet  | 1999-11-19 |
|  3 | sitajul       | 1950-03-12 |
|  7 | jjbrig        | 1956-03-01 |
|  8 | jj            | 1956-03-01 |
+----+---------------+------------+

 

i'm at a lost...

[fenway]

Me too... what do you want in the final output?

[XRayden]

what i need is this .

 

the top 5 per average of videos like this query does :

SELECT `BTBS_Activites`.`VID`, AVG(`BTBS_Activites`.`Result`) as moyenneVote 
FROM `BTBS_Activites`,`BTBS_Videos` 
WHERE `BTBS_Activites`.`Action` = 'Vote' 
AND `BTBS_Videos`.`State` = 'Online' 
AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID` 
GROUP BY `BTBS_Activites`.`VID` ORDER BY moyenneVote DESC;

 

but video(s) (`BTBS_Videos`) from wich the user (`BTBS_Membres`) who uploaded it is under an age restriction (more than 20, or between 13 and 19)

 

they are linked this way :

`BTBS_Activites`.`VID` -> `BTBS_Videos`.`ID`

`BTBS_Videos`.`MID` -> `BTBS_Membres`.`ID`

 

[fenway]

Personally, I prefer something similar to the following for age: "DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0"... easier to read, no string hacks!

 

You need to take your age query, add the uid to the select list, and then inner join that resulting table to te query you posted above with the restriction you desire in your where clause.

[XRayden]

how do we do that kind of thing? first time i've heard of a selected select query?

[fenway]

It's called a derived table... just write the query and wrap it in parents, and put it in place of where you would put the table name.

[XRayden]

Finaly i've arrived at this kind of request :

 

SELECT `BTBS_Videos`.`ID`, 
AVG( `BTBS_Activites`.`Result` ) AS `moyenneVote`
FROM `BTBS_Activites`, `BTBS_Videos`, (SELECT `ID` FROM `BTBS_Membres` WHERE DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( `DDN` ) ) , '%Y' ) +0 > '10') as `Members`
WHERE `BTBS_Activites`.`Action` = 'Vote'
AND `BTBS_Videos`.`State` = 'Online'
AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID`
AND `Members`.`ID` = `BTBS_Videos`.`MID`
GROUP BY `BTBS_Activites`.`VID`
ORDER BY `moyenneVote` DESC

 

that work pretty well and compile in 0.0010 sec

 

that'll be a very big big big thank you! and now i know a little more about MySQL.

 

i'm a pro in PHP and right now i'm trying to take as much as possible of the cpu in php calculation.

[fenway]

I'd prefer proper JOIN syntax, but that's the idea.