CODEWITHSUNDEEP
mysqlsqljoingroup-by
Sourav
Sourav

Reputation: 17530

Joining 2 tables with Group By

poll_opts table for storing options and poll_voted for storing vote result, pid stands for poll id(unique) and oid stands for option id(unique for individual poll only)

poll_voted [Primary Key: pid.oid.emp] 

+-----+-----+-------+
| pid | oid | emp   |
+-----+-----+-------+

poll_opts [Primary Key: pid.oid] 

+-----+-----+---------+
| pid | oid | opt     |
+-----+-----+---------+

pid & oid type: int , opt type: text

table data

Upvotes: 1

Views: 74

Answers (1)

Patrick Artner
Patrick Artner

Reputation: 51683

If you need the "not existent" results as well you need a left outer join preserves all results from poll_opts even if no match in poll_votes is found.

MySql 5.7 Join Syntax

Query:

select opt, count(vo.oid) 
from poll_opts po
left outer join poll_voted vo on vo.oid = po.oid and po.pid=vo.pid
where po.pid = 3 -- 3 
group by opt

Output:

opt       count(vo.oid)
Chrome    0
Firefox   0
IE        0
MS Edge   0
Opera     1

Testdata:

CREATE TABLE poll_voted    (`pid` int, `oid` int, `emp` int);

INSERT INTO poll_voted     (`pid`, `oid`, `emp`)
VALUES
    (1, 0, 1989),
    (1, 2, 1989),
    (1, 4, 1989),
    (1, 6, 1989),
    (3, 2, 1989)  
;


CREATE TABLE poll_opts     (`pid` int, `oid` int, `opt` varchar(15));

INSERT INTO poll_opts      (`pid`, `oid`, `opt`)
VALUES
    (1, 0, 'WinXP'),
    (1, 2, 'wIN7'),
    (1, 4, 'wIN 10'),
    (1, 6, 'Ubuntu'), 
    (3, 0, 'IE'),
    (3, 1, 'MS Edge'),
    (3, 2, 'Opera'),
    (3, 3, 'Chrome'),
    (3, 4, 'Firefox')
;

Upvotes: 1

Related Questions