CODEWITHSUNDEEP
phpmysqlsql
Rich
Rich

Reputation: 1779

MySQL query: generate a report of the top votes for the each food type

I have a user table with id, username and food_id columns. The idea is the users store their favourite food and we come up with a league table of foods. I want to generate a report of the top votes for the each food type. I am using MySQL and PHP.

For clarity, here is an example of the table:

 id  food_id   username
 1   1         Bob
 2   100       Jane
 3   200       Andy
 4   1         Maggy
 5   100       Rich
 6   100       Mick
 7   1         Kevin

I have a query that user 'renegm' on Stackoverflow gave me. It give me the results of the food survey. The query is:

select food_id, count(*) score
   from myTable
group by food_id
order by score desc limit 100

It gives me the results perfectly as in:

food_id   score
1         3
100       4

I realised after I got the answer that I am getting food_ids and not names.

I need to do a join on food table. It looks like

food_id    food_name
1          Salad
100        Burgers

How do I incorporate the join into the above query? I have looked at my books but can't quite work it out.

Upvotes: 0

Views: 148

Answers (5)

add9
add9

Reputation: 1503

SELECT column_name(s) FROM myTable INNER JOIN food_table ON myTable.food_id=food_table.food_id

will join two tables providing that your table name is is food_table. Adjust the query with desired column names , order and group by

hope it helps

Upvotes: 1

Jon Black
Jon Black

Reputation: 16559

Why dont you do something like the following which uses triggers to maintain the ratings so all you need to do is a very simple query to get the result you want:

This method will be much more performant as your tables grow.

Hope it helps

Example query

select * from food order by rating desc;
+---------+--------+-----------+-------------+--------+
| food_id | name   | num_votes | total_score | rating |
+---------+--------+-----------+-------------+--------+
|       1 | food 1 |         6 |          19 |   3.17 |
|       3 | food 3 |         2 |           6 |   3.00 |
|       2 | food 2 |         3 |           7 |   2.33 |
+---------+--------+-----------+-------------+--------+
3 rows in set (0.00 sec)

full script

drop table if exists food;
create table food
(
food_id int unsigned not null auto_increment primary key,
name varchar(255) not null,
num_votes int unsigned not null default 0,
total_score int unsigned not null default 0,
rating decimal(8,2) not null default 0
)
engine = innodb;

drop table if exists food_vote;
create table food_vote
(
food_id int unsigned not null,
user_id int unsigned not null,
score tinyint unsigned not null default 0,
primary key (food_id, user_id)
)
engine=innodb;

delimiter #

create trigger food_vote_after_ins_trig after insert on food_vote
for each row
begin
 update food set 
    num_votes = num_votes + 1,
    total_score = total_score + new.score,
    rating = total_score / num_votes  
 where 
    food_id = new.food_id;
end#

delimiter ;

insert into food (name) values ('food 1'),('food 2'), ('food 3');

insert into food_vote (food_id, user_id, score) values
(1,1,5),(1,2,4),(1,3,3),(1,4,2),(1,5,1),(1,6,4),
(2,1,2),(2,2,1),(2,3,4),
(3,1,4),(3,5,2);

Upvotes: 2

d-live
d-live

Reputation: 8036

select 
    (SELECT food_name from food_table food where food.id = mt.food_id) foodName, 
    count(*) score    
from myTable mt
group by food_id 
order by score desc limit 100

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212412

select f.food_id,
       n.food_name,
       count(f.food_id) score
  from myTable
  left join food_names n 
         on n.food_id = f.food_id
group by f.food_id,
         n.food_name
order by score desc limit 100

Upvotes: 2

Matej Baćo
Matej Baćo

Reputation: 1332

Here you go:

select 
   myTable.food_id, 
   food.food_name,
   count(myTable.id) score
from 
   myTable
   join food on (food.food_id = myTable.food_id)
group by 
   myTable.food_id
order by 
   myTable.score desc 
limit 100

Upvotes: 1

Related Questions