MySql query returning entire table with possible values from a second table

Question

I have 2 tables:

event_categories containing:

event_category_id, event_category

Sample data:

1, Tennis
2, Volleyball
3, Boxing
4, Skating

Then I have a table that joins users that might possibly be linked to any of these categories.

users_event_categories containing

user_id, event_category_id

Sample data:

1223, 2
1223, 4
5998, 2

I need a query that returns ALL event categories, and returns if a user has that category linked.

So if I query with the user_id 1223 my result would be:

1, Tennis, 0
2, Volleyball, 1
3, Boxing, 0
4, Skating, 1

Or a query with user_id 4444 would return:

1, Tennis, 0
2, Volleyball, 0
3, Boxing, 0
4, Skating, 0

score 2 · Accepted Answer · answered Aug 01 '14 at 13:09

2

This would work if you only want data about one particular user

select ec.event_category_id, ec.event_category, if(uec.user_id is null, 0, 1) 
from event_categories ec 
    left join users_event_categories uec
        on uec.event_category_id = ec.event_category_id and uec.user_id = 1223

answered Aug 01 '14 at 13:09

Tegi

768
8
14

You were faster :) Here's a fiddle with the working solution: http://sqlfiddle.com/#!2/1b7143/8/0 – jbrosi Aug 01 '14 at 13:13
I was unaware that it is possible to do an additional AND statement with a join statement. Also, interestingly replacing on uec.event_category_id = ec.event_category_id with USING(event_category_id) does not work. – Michael Frey Aug 01 '14 at 13:21
This is a good explanation: http://stackoverflow.com/questions/11366006/mysql-on-vs-using – Tegi Aug 01 '14 at 13:25
@jbrosi Was just looking at your jsfiddle as well. It only works with the id 1223. Another id skips some results. – Michael Frey Aug 01 '14 at 13:34
@MichaelFrey sorry my fault, fixed the fiddle: http://sqlfiddle.com/#!2/1b7143/22 – jbrosi Aug 01 '14 at 13:41

Torrezzzz · Answer 2 · 2014-08-01T13:05:27.737

0

select tn2.user_id,event_category,count(event_category) as total from table_name1 tn1
inner join table_name2 tn2 on tn1.event_category_id = tn2.event_category_id
where tn2.user_id = 4444
group by event_category

edited Aug 01 '14 at 13:05

answered Aug 01 '14 at 12:58

Torrezzzz

307
2
13

Thanks for the answer, but this only returns the category where the users are linked to, and excludes all others. – Michael Frey Aug 01 '14 at 13:04

MySql query returning entire table with possible values from a second table

2 Answers2