[SOLVED] Complex SQL Joining

[ionik]

Hello,

 

I am fixing a few bugs with a site and have quite a problem with a SQL query ....

What we are doing is pulling information for a video sponsors profiles, for the projects that they host for users to upload videos to the information we are getting is views for each video and # of videos for each project.

 

To do this I need to pull information from 5 Tables in this breakdown

 

Users
  |- SELECT userId and Username
     | - SELECT users group ID
     | - GET promotions for this user
         | - Count Number of videos for this promotion
         | - GET videos for this promotion
             | - Count Number of Views for each video

 

Here is the SQL I have

 

SELECT DISTINCT u.userName, u.userId, v.videoCount, v.viewCount, g.groupId
    FROM user u
        LEFT JOIN (
            SELECT sv.viewCount, sv.videoCount, userId, promoId FROM promos
                LEFT JOIN (
                    SELECT COUNT(*) AS videoCount, vv.viewCount, videoId, promoId FROM videos
                        LEFT JOIN (
                            SELECT videoId, COUNT(*) AS viewCount FROM videoTracking GROUP BY videoId) AS vv USING (videoId)
                        GROUP BY promoId) AS sv USING(promoId)
        ) AS v USING (userId)
        LEFT JOIN (
                    SELECT userid, groupId FROM userGroups ) AS g USING (userId)
    WHERE g.groupId = "3" ORDER BY u.userId DESC LIMIT 0,10

 

and Here are the table structures

 

mysql> EXPLAIN videos;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| videoId   | int(11)     | NO   | PRI | NULL    | auto_increment | 
| date      | varchar(14) | NO   |     | NULL    |                | 
| promoId   | int(11)     | NO   | MUL | NULL    |                | 
| channelId | int(11)     | NO   | MUL | NULL    |                | 
| userId    | int(11)     | NO   | MUL | NULL    |                | 
| status    | tinyint(1)  | NO   |     | 0       |                | 
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> EXPLAIN userGroups;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| userGroupId | int(11) | NO   | PRI | NULL    | auto_increment | 
| groupId     | int(11) | NO   |     | NULL    |                | 
| userId      | int(11) | NO   |     | NULL    |                | 
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> EXPLAIN user;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| userId    | int(11)     | NO   | PRI | NULL    | auto_increment | 
| userName  | varchar(20) | NO   |     | NULL    |                | 
| salt      | varchar(4)  | NO   |     | NULL    |                | 
| password  | varchar(45) | NO   |     | NULL    |                | 
| timestamp | varchar(14) | NO   |     | NULL    |                | 
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> EXPLAIN videoTracking ;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| trackId   | int(11)     | NO   | PRI | NULL    | auto_increment | 
| promoId   | varchar(11) | NO   | MUL | NULL    |                | 
| channelId | int(11)     | NO   | MUL | NULL    |                | 
| userId    | int(11)     | NO   | MUL | NULL    |                | 
| viewerId  | int(11)     | NO   |     | NULL    |                | 
| viewerIp  | varchar(50) | NO   |     | NULL    |                | 
| videoId   | int(11)     | NO   | MUL | NULL    |                | 
| timestamp | varchar(14) | NO   |     | NULL    |                | 
+-----------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> EXPLAIN promos;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| promoId   | int(11)     | NO   | PRI | NULL    | auto_increment | 
| userId    | int(11)     | NO   | MUL | NULL    |                | 
| name      | varchar(75) | NO   |     | NULL    |                | 
| channelId | int(11)     | NO   | MUL | NULL    |                | 
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

[ionik]

SOLVED FINAL SQL

SELECT g.userId, un.groupId, g.userName, pr.videoCount, pr.viewCount
FROM user g
LEFT JOIN (

SELECT groupId, userId
FROM userGroups
) AS un
USING ( userId )
LEFT JOIN (

SELECT userId, promoId, SUM( vd.videoCount ) AS videoCount, SUM( vd.views ) AS viewCount
FROM promos
LEFT JOIN (

SELECT promoId, videoId, COUNT( videoId ) AS videoCount, SUM( vt.viewCount ) AS views
FROM videos
LEFT JOIN (

SELECT COUNT( trackId ) AS viewCount, videoId
FROM videoTracking
GROUP BY videoId
) AS vt
USING ( videoId )
GROUP BY videoId
) AS vd
USING ( promoId )
GROUP BY userId
) AS pr
USING ( userId )
WHERE groupId = "3"
LIMIT 0 , 10