CODEWITHSUNDEEP
mysql
Salil
Salil

Reputation: 47472

How to find out the data depending on the count in association table

I have events table like following

id    name 
 1    Ind Vs Pak
 2    Fedrer Vs Nadal
 3    MI Vs RR

event_participations

 id   participant_type  participant_id event_id
  1      Team               1             1
  2      Team               2             1
  3      Athlete            1             1
  4      Athlete            2             1
  5      Athlete            3             1
  6      Athlete            4             2
  7      Athlete            5             2
  8      Team               3             3
  9      Team               4             3
  10     Athlete            6             3
  11     Athlete            7             3
  12     Athlete            8             3

I want all the events which have at least one Team like following

id    name 
 1    Ind Vs Pak
 3    MI Vs RR

Upvotes: 2

Views: 32

Answers (3)

Naveen Kumar Alone
Naveen Kumar Alone

Reputation: 7668

Here is SQLFiddle

SELECT e.id, e.name FROM events e 
  JOIN event_participations ep ON ep.event_id = e.id 
  WHERE ep.participant_type = 'Team' 
  GROUP BY e.id 
  HAVING COUNT(e.id) > 1;

Shows output as

 | ID |       NAME |
-|----|------------|-
 |  1 | Ind Vs Pak |
 |  3 |   MI Vs RR |

Upvotes: 1

Deepak Rai
Deepak Rai

Reputation: 2203

SELECT  * FROM events AS e
LEFT JOIN event_participants AS ep
ON (e.id = ep.event_id)
WHERE ep.participant_type = 'team'
GROUP BY ep.event_id
HAVING COUNT(ep.event_id) > 1

sqlfiddle demo : http://sqlfiddle.com/#!2/09ae7/6

Upvotes: 0

jhnesk
jhnesk

Reputation: 29

you should use "having". Try this:

select e.* from events e join event_participations ep on ep.event_id = e.id where ep.participant_type = 'Team' group by e.* having count(*) > 1

Updated: I tought you meant more than one. A simple join should do it if you just need at least one

Upvotes: 0

Related Questions