"favorite" column mysql

Question

Sorry for the question, it was a bit vague, let me rephrase my question.

Tables:

• users: id, name
• activities: id, when, tagId
• tags: id, name

So the user has activities, each activity can have a tag for example "reading mail" would be a tag for an activity. So lets take an example:

• user: 1, mike
• activity: 1, today, 1
• tag: 1, reading email

And the user mike has another activity:

• activity: 2, tomorrow, 2
• tag: 1, reading email

Now you can see the user "mike" has 2 activities with "reading email", so when the user creates a lot of activities, I need to get the favorite (most used) tag.

Answer 1

The following SQL will return the name of the tag that is used the most by a specific user (in this case with is 123)

if you want a listing of tags based on how often they have been used, you can change the number in the limit 1.

select tags.name 
from tags 
left join items on tags.id=items.tagId 
where items.userId=123
group by tags.name 
order by count(items.id) 
desc limit 1;