Penalty Records - GROUP BY; HAVING; UNION;

Query for yellow card:

with a given Group, so its id is given.

with a given Event name, so its e_id is given

with the current season for each tournament, so the 5 seasons' ids are given, then the matchDays' ids can be inferred.

 

SELECT me.*, t.t_name, pl.first_name, pl.last_name 
FROM jos_bl_match_events AS me, 
jos_bl_teams AS t, 
jos_bl_players AS pl,
jos_bl_grteams AS gt,
jos_bl_match AS m
WHERE me.e_id = 1 
AND me.t_id = t.id 
AND me.player_id = pl.id 
AND gt.t_id = me.t_id 
AND gt.g_id = 4 
AND m.id = me.match_id 
AND m.m_id in (2,3)

SUM

SELECT SUM(me.ecount) AS yellowc, me.*, t.t_name, pl.first_name, pl.last_name 
FROM jos_bl_match_events AS me, 
jos_bl_teams AS t, 
jos_bl_players AS pl,
jos_bl_grteams AS gt,
jos_bl_match AS m
WHERE me.e_id = 1 
AND me.t_id = t.id 
AND me.player_id = pl.id 
AND gt.t_id = me.t_id 
AND gt.g_id = 4 
AND m.id = me.match_id 
AND m.m_id in (2,3) 
GROUP BY me.player_id

Refs:

http://www.java2s.com/Code/SQL/CatalogSQL.htm(important)

http://www.tizag.com/sqlTutorial/sqlgroupby.php

http://webdesign.kerthis.com/sql/sql_left_join(tw)

http://xianglp.iteye.com/blog/868957

Oracle

http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/UsingMultipleColumnsinaGroup.htm

http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/GroupingatMultipleLevels.htm

http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/Bornafter19600101groupbydepartmentnumberwithcount4.htm

http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/GettingValuesandSubtotalsinOneGowithUNION.htm