Specific Mysql Select query Help needed

Question

I have two tables resource (id, name, ...) and user_favorites(user_id, resource_id) which has foreign key resource_id from the resource table.

I have a situation where I need to check if resource table has any records to user_favorites table for a particular user_id in user_favorites table, and put a flag true or false accordingly.

This is the last query I could come up with

SELECT distinct resources.*,
       (CASE WHEN user_favorites.user_id='1' then 'TRUE' ELSE 'FALSE' END) as favorite,
       user_favorites.user_id
FROM resources LEFT JOIN
     user_favorites
     ON resources.id = user_favorites.resource_id

However, what I want to be able to keep the resources. With the current query, it records false when there are records for other user ids also.

What is the best way to approach this problem?

one way is this: you'd need a WHERE clause to restrict to the desired user ID. In the CASE you can then just check whether favourites.user_id is null or not. Haven't tested that obviously but I'm fairly sure it would work. — ADyson
– ADyson, Commented Aug 1, 2018 at 10:59
@ADyson I want to be able to show all resource records without restricting the ID, but be able to put up a flag TRUE or FALSE based on whether there are any records on the user favorites table for that particular ID — Kavinda Keshan Rasnayake
– Kavinda Keshan Rasnayake, Commented Aug 1, 2018 at 11:01
Add example data and expected results as text formatted data table we are geussing here.. Place the SQL example data on sqlfiddle.com or db-fiddle.com — Raymond Nijland
– Raymond Nijland, Commented Aug 1, 2018 at 11:05

Gordon Linoff · Accepted Answer · 2018-08-01 10:59:46Z

2

You can use expression logic in the select. In MySQL, you don't even need case:

SELECT r.*, 1 as user_id,
       (EXISTS (SELECT 1
                FROM user_favorites uf
                WHERE r.id = ur.resource_id AND
                      ur.user_id = 1
               )
       ) as flag
FROM resources r ;

answered Aug 1, 2018 at 10:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Kavinda Keshan Rasnayake Over a year ago

Thank you for the answer. I will accept the answer in 5 minutes

Al- Imran Khan · Accepted Answer · 2018-08-01 11:12:28Z

0

Use this

SELECT 
r.*
, IFNULL(ur.totalUser, 0) AS totalUser
FROM `resource` r
LEFT JOIN 
(
    SELECT 
    resource_id
    , COUNT(user_id) AS totalUser
    FROM `user_favourites` 
    GROUP BY resource_id
) AS ur ON ur.resource_id = r.`id`

answered Aug 1, 2018 at 11:12

Al- Imran Khan

1871 gold badge5 silver badges14 bronze badges

Collectives™ on Stack Overflow

Specific Mysql Select query Help needed

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related