CODEWITHSUNDEEP
mysqlsqljoin
Tengu
Tengu

Reputation: 67

Joining multiple sql tables to find common value

I have two sql tables that I am trying to join together. I'm trying to make it so that a userid from my first table- table "video" and the posted_by from table "review" are joined so that the output lists a user pair where that they always gave each other "five” for the "star" column

I was working with this statement, but it's not yielding any results. There are only two users in the tables that fit the requirement: [email protected] and [email protected]

Query:

SELECT U1.userid, U2.userid
FROM video U1, video U2
WHERE U1.userid < U2.userid   
            (SELECT COUNT(*)         
            FROM video
            WHERE userid = U1.userid) = 
             (SELECT COUNT(J.star) FROM video J, review R
             WHERE  J.userid = U1.userid AND  J.star = R.star AND R.posted_by = U2.userid AND R.star = "5") 
                            review
            (SELECT COUNT(*)         
            FROM video
            WHERE userid = U2.userid) = 
             (SELECT COUNT(J1.star
             FROM video J1, review R1
             WHERE  J1.userid = U2.userid AND  J1.star = R1.star AND R1.posted_by = U1.userid AND R1.star = "5");

Table video:

+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| video_url                                   | video_name                     | video_description                      | video_subject                            | video_city    | video_tags | star | date       | userid            | reviewed_by       |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://www.youtube.com/watch?v=xGa9AbKO-4s | Nice video of Detroit          | Watch as someone shows you Detroit     | Downtown Detroit                         | Detroit       | lake       | 5    | 2019-11-29 | [email protected] | [email protected]   |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/5KE7ppUIPhQ                | Sacaomento Forest              | A nice forest in Sacromento            | Forest in Sacramento                     | Sacramento    | lake       | 3    | 2019-11-30 | [email protected]     | NULL              |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/5KE7ppUIPhQ1               | Sacromento Forest              | A nice forest in Sacromento            | Forest in Sacromento                     | Sacromento    | lake       | 5    | 2019-12-01 | [email protected]     |                   |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/BnbE6QzKY-I                | Great video of downtown Dallas | See a tour of downtown Dallas          | Downtown Dallas                          | Dallas        | lake       | 0    | 2019-12-01 | [email protected]    | NULL              |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/HGvwO8qq8FI                | Tour of Cape Cod               | See a tour of Cape Code                | View of Cape Code                        | Cape Code     | lake       | 5    | 2019-12-02 | [email protected]   | [email protected] |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/HiZXABMNCUY                | Tour of Manhattan              | See Manhattan on the big screen        | Downtown New york                        | Manhattan     | lake       | 2    | 2019-11-27 | [email protected]     | NULL              |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/ORoyGEgvfXY                | Nice video of Orlando          | See the appeals of Orlando             | Downtown Orlando                         | Orlando       | lake       | 4    | 2019-11-28 | [email protected] | NULL              |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/pKwuW06NvXM                | Scenic Downtown Cincinatti     | Take the backroutes through Cincinatti | Take the backroutes through Cincinatties | Cincinatti    | lake       | 3    | 2019-11-26 | [email protected]  | NULL              |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/QSwvg9Rv2EI                | Tour of Chicago                | Let us bring Chicago to you            | Downtown Chicago                         | Chicago       | lake       | 2    | 2019-11-25 | [email protected]    | NULL              |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/qY02yEvqFio                | Downtown Detroit               | Tour of Downtown Detroit               | Downtown Detroit                         | Detroit       | lake       | 5    | 2019-11-27 | [email protected]   | NULL              |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+
| https://youtu.be/_QH_tdYR3iI                | Great video of Lake Erie       | See the great lakes up close           | Michigan great lakes                     | Traverse City | lake       | 0    | 2019-11-27 | [email protected] | NULL              |
+---------------------------------------------+--------------------------------+----------------------------------------+------------------------------------------+---------------+------------+------+------------+-------------------+-------------------+

Table review:

+--------------------------------+--------------------------+-------------+-------------------+
| video_name                     | review_comments          | review_star | posted_by         |
+--------------------------------+--------------------------+-------------+-------------------+
| Nice video of Detroit          | cool dude                | 2           | [email protected]    |
+--------------------------------+--------------------------+-------------+-------------------+
| Tour of Chicago                | cool video               | 5           | [email protected]  |
+--------------------------------+--------------------------+-------------+-------------------+
| Tour of Cape Code              | could have been better   | 3           | [email protected]   |
+--------------------------------+--------------------------+-------------+-------------------+
| Great video of downtown Dallas | good video               | 5           | [email protected]     |
+--------------------------------+--------------------------+-------------+-------------------+
| Great video of downtown Dallas | great video              | 0           | [email protected]     |
+--------------------------------+--------------------------+-------------+-------------------+
| Nice video of Detroit          | great video!             | 2           | [email protected]     |
+--------------------------------+--------------------------+-------------+-------------------+
| Great video of Lake Erie       | i really didnt like this | 0           | [email protected] |
+--------------------------------+--------------------------+-------------+-------------------+
| Great video of downtown Dallas | it was terrible          | 0           | [email protected]     |
+--------------------------------+--------------------------+-------------+-------------------+
| Great video of downtown Dallas | Nice van                 | 4           | [email protected] |
+--------------------------------+--------------------------+-------------+-------------------+
| Nice video of Detroit          | nice video               | 5           | [email protected]    |
+--------------------------------+--------------------------+-------------+-------------------+
| Sacramento Forest              | okay video               | 3           | [email protected] |
+--------------------------------+--------------------------+-------------+-------------------+
| Scenic Downtown Cincinatti     | terrible video           | 0           | [email protected]   |
+--------------------------------+--------------------------+-------------+-------------------+
| Nice video of Orlando          | wonderful video          | 5           | [email protected] |
+--------------------------------+--------------------------+-------------+-------------------+

Upvotes: 0

Views: 38

Answers (1)

danblack
danblack

Reputation: 14736

To solve the problem I'm going to start at some basic queries and work towards the answer asked.

Start by collating the the videos and the reviews with a join

SELECT v.video_name, AVG(review_star)
FROM video v
JOIN review r
  USING (video_name)
GROUP BY v.video_name

Then we can look at the average star rating for reviewers:

SELECT r.posted_by, AVG(review_star)
FROM review r
GROUP BY r.posted_by

Take a step further and group by the poster/review pair:

SELECT v.userid, r.posted_by, AVG(review_star)
FROM video v
JOIN review r
  USING (video_name)
GROUP BY v.userid, r.posted_by

HAVING allows a post filter on this. All 5 star rating will have an average of 5.

SELECT v.userid, r.posted_by, AVG(review_star) as avg
FROM video v
JOIN review r
  USING (video_name)
GROUP BY v.userid, r.posted_by
HAVING avg = 5

Upvotes: 1

Related Questions