Adding a Subquery to a Query in SQL

Question

I have a query that works very well. Let me start with it:

Edit: The SQL has been updated. I get 0 in every row.

   SELECT i.item, i.user_id, u.username,
        (COALESCE(r.ratetotal, 0)) AS total,
        (COALESCE(c.commtotal, 0)) AS comments,
        (COALESCE(r.rateav, '50%')) AS rate,
        (COALESCE(x.wasRated, '0')) AS wasRated
        FROM items AS i
        LEFT JOIN master_cat AS c
            ON (c.cat_id = i.cat_id)
        LEFT JOIN users AS u 
        ON u.user_id = i.user_id    
        LEFT JOIN
            (SELECT item_id, 
            COUNT(item_id) AS ratetotal, 
            AVG(rating) AS rateav 
            FROM ratings GROUP BY item_id) AS r 
        ON r.item_id = i.item_id
        LEFT JOIN
            (SELECT item_id, 
            COUNT(item_id) AS commtotal 
            FROM reviews GROUP BY item_id) AS c
        ON c.item_id = i.item_id
        LEFT JOIN
            (SELECT xu.user_id, ra.item_id, '1' AS wasRated
            FROM users AS xu
            LEFT JOIN ratings AS ra
                ON ra.user_id = xu.user_id
            WHERE xu.user_id = '1') AS x 
         ON x.user_id = u.user_id
              AND x.item_id = r.item_id
        WHERE c.category = 'Movies' 
        ORDER by i.item ASC;

I need to add one more function to it, where you see AS x

Basically, there are three tables here that are important. items, reviews and ratings. In the top portion you see there are subqueries that are taking statistics such as averages and totals for each item.

I need a final query that is tied to user_id, item_id and rate_id (in ratings). In the end result, where it list each item and the stats with it, I want one more column, a simple true or false if logged in user has rated it. So I need something like this:

SELECT ???
FROM ratings AS r
WHERE r.user_id = '{$user_id}'

(user_id of logged in user is passed in from PHP.`)

How can I make a subquery that gives me that last bit of info, but puts it in each row of items in the parent query?

You'll probably get more responses if you post the SQL DDL for your tables, and some SQL INSERT statements, too. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Feb 16, 2013 at 22:33

Dan Bracuk · Accepted Answer · 2013-02-16 23:01:34Z

1

Add this to the parent query.

, coalesce(x.WasRated, 'false') as WasRated

Your x subquery is:

(select users.user_id
 , ratings.item_id
, 'true' WasRated
from users join ratings on user.user_id = ratings.user_id
where users.user_id = the one for the logged in user
) x on x.user_id = users.user_id
and x.item_id = ratings.item_id

or something like it.

answered Feb 16, 2013 at 23:01

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TheLettuceMaster Over a year ago

I have updated my code based on your submission and also included result. Any thoughts?

Dan Bracuk Over a year ago

My suggestion was to do an inner join from users to ratings in the subquery. You have a left join. My intent was that the subquery would return 'true' if the user rated an item. The coalesce function in the main query would change that to false if he didn't. Putting a left join in the subquery will make it return true for every user.

TheLettuceMaster Over a year ago

I just changed it to an inner join and made some very tiny tweaks and got it working! Marked correct, thanks!

Collectives™ on Stack Overflow

Adding a Subquery to a Query in SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related