CODEWITHSUNDEEP
mysql
HerrimanCoder
HerrimanCoder

Reputation: 7218

Compressing 2 data rows except for a few columns

I have a set of data that shows sports referee game schedules. In almost all cases, 2 referees work together for each game. Here is my query:

SELECT DISTINCT g.GroupName, date(s.EventDate) AS EventDate, DATE_FORMAT(s.EventDate,'%H:%i:%s') AS EventTime, l.SiteName, ss.SubSiteName, u.Email, u.FirstName, u.LastName, u.Phone1 
FROM Schedule s 
JOIN Groups g 
ON s.GroupID = g.GroupID 
LEFT OUTER JOIN Sites l 
ON s.SiteID = l.SiteID 
LEFT OUTER JOIN SubSites ss 
ON l.SiteID = ss.SiteID 
LEFT OUTER JOIN Users u 
ON s.UserID = u.UserID 
WHERE g.OrganizationID = 2
AND s.EventDate > DATE_ADD(NOW(), INTERVAL 0 HOUR) 
AND (s.SubSiteID = 0 OR s.SubSiteID = ss.SubSiteID) 
ORDER BY l.SiteName, ss.SubSiteName, s.EventDate, u.LastName

And here are some of the records produced by that query:

GroupName       EventDate   EvtTime  SiteName               SubSite         Email             FirstName LastName    Phone1
==============================================================================================================================
Hoops Forever   8/10/2016   8:00     Kennedy High School    Court #33 BMJ   [email protected]  Jeff      Smith       8014445555
Hoops Forever   8/10/2016   8:00     Kennedy High School    Court #33 BMJ   [email protected]    Kris      Jones       8013331111
Hoops Forever   8/10/2016   9:20     Kennedy High School    Court #33 BMJ   [email protected]  Jeff      Smith       8014445555
Hoops Forever   8/10/2016   9:20     Kennedy High School    Court #33 BMJ   [email protected]    Kris      Jones       8013331111
Hoops Forever   8/10/2016   10:40    Kennedy High School    Court #33 BMJ   [email protected]  Jeff      Smith       8014445555
Hoops Forever   8/10/2016   10:40    Kennedy High School    Court #33 BMJ   [email protected]    Kassy     Hanks       8019997777
Hoops Forever   8/10/2016   12:00    Kennedy High School    Court #33 BMJ   [email protected]    Michael   Rogers      8010001111
Hoops Forever   8/10/2016   12:00    Kennedy High School    Court #33 BMJ   [email protected]    Kris      Jones       8013331111
Hoops Forever   8/10/2016   13:20    Kennedy High School    Court #33 BMJ   [email protected]    Michael   Rogers      8010001111
Hoops Forever   8/10/2016   13:20    Kennedy High School    Court #33 BMJ   [email protected]    Kris      Jones       8013331111

Now look at the top 2 rows and notice that all values between the 2 rows are the same except for the last 4 columns, which are specific to the referee. So what I tried was adding this:

GROUP BY GroupName, EventDate, EvtTime, SiteName, SubSite

And that got me halfway there, but I lost the 2nd referee in each grouping:

GroupName       EventDate   EvtTime  SiteName               SubSite         Email             FirstName LastName    Phone1
==============================================================================================================================
Hoops Forever   8/10/2016   8:00     Kennedy High School    Court #33 BMJ   [email protected]  Jeff      Smith       8014445555
Hoops Forever   8/10/2016   9:20     Kennedy High School    Court #33 BMJ   [email protected]  Jeff      Smith       8014445555
Hoops Forever   8/10/2016   10:40    Kennedy High School    Court #33 BMJ   [email protected]  Jeff      Smith       8014445555
Hoops Forever   8/10/2016   12:00    Kennedy High School    Court #33 BMJ   [email protected]    Michael   Rogers      8010001111
Hoops Forever   8/10/2016   13:20    Kennedy High School    Court #33 BMJ   [email protected]    Michael   Rogers      8010001111

And without further ado, here's what I really need:

GroupName       EventDate   EvtTime  SiteName               SubSite         Email1            FN1       LN1      Phone1       EMail2          FN2     LN2      Phone2
=========================================================================================================================================================================
Hoops Forever   8/10/2016   8:00     Kennedy High School    Court #33 BMJ   [email protected]  Jeff      Smith    8014445555   [email protected]  Kris    Jones    8013331111
Hoops Forever   8/10/2016   9:20     Kennedy High School    Court #33 BMJ   [email protected]  Jeff      Smith    8014445555   [email protected]  Kris    Jones    8013331111
etc...........

Notice that in my desired result set, the 4 columns that are different (ref-specific rather than game-specific) are appended into the "compressed" row. Logically it makes sense: There is 1 game, but 2 refs. I want the info for that 1 game to be in 1 row, and I want the info for the 2 refs to also be in the same row, appended to the end. In the actual DB there are 2 rows, but I want them combined and appended. In cases where there is no referee info, I just want nulls/blanks there -- nothing fancy.

So how is the sql constructed for this? Thanks so much in advance!

Upvotes: 0

Views: 24

Answers (1)

Oleg Imanilov
Oleg Imanilov

Reputation: 2751

If it is acceptable to store both refs in same column - you may use:

GROUP_CONCAT(column SEPARATOR ',')

Another solution when you sure there is only two rows in a group:

  1. Get max(email) for first and min(email) for second
  2. Get rest of referees fields from joined table by email.

Upvotes: 1

Related Questions