MySQL Union (or similar) query

I have some booking data from a pair of views in MySQL. They match columns perfectly, and the main difference is a booking code that is placed in one of these rows.

The context is as follows: this is for calculating numbers for a sports camp. People are booked in, but can do extra activities.

View 1: All specialist bookings (say: a football class).

View 2: A general group.

Due to the old software, the booking process results in many people booking for the general group and then are upgraded to the old class. This is further complicated by some things elsewhere in the business.

To be clear - View 1 actually contains some (but are not exclusively all) people from within View 2. There's an intersection of the two groups. Obviously people can't be in two groups at once (there's only one of them!).

Finding all people who are in View 2 is of course easy... as is View 1. BUT, I need to produce a report which is basically:

  • "View 1" overwriting "View 2"... or put another way:
  • "View 1" [sort of] UNION "View 2"

However: I'm not sure the best way of doing this as there are added complications:

Each row is as approximately (with other stuff omitted) as follows:

User ID Timeslot Activity
1          A     Football
1          A     General
2          A     General
3          A     Football

As you can see, these rows all concern timeslot A: - User 2 does general activities. - User 3 does football. - User 1 does football AND general.

AS these items are non unique, the above is a UNION (distinct), as there are no truly distinct rows.

The output I need is as follows:

User ID Timeslot Activity
1          A     Football
2          A     General
3          A     Football

Here, Football has taken "precedence" over "general", and thus I get the picture of where people are at any time.

This UNION has a distinct clause on a number of fields, but ignores others.

So: does anyone know how to do what amounts to:

  • "add two tables together and overwrite one of them if it's the same timeslot"

Or something like a:

  • "selective distinct on UNION DISTINCT".

Cheers Rick


ANSWERS:


Try this:

SELECT *
FROM
  (SELECT *,
          IF(Activity='General',1,0) AS order_column
   FROM `Table1`
   ORDER BY order_column) AS tmp
GROUP BY UserId

This will add an order_column to your original table that as value 1 if the Activity value is general; Doing this we can select this temporary table ordering by this column (ascending order) and all record with general activity comes after all others. After that we can simply select the result of this temporary table grouping by user id. The group by clouse without any aggregate function takes the first record that match.

EDIT: If you don't to use group by without aggregate function this is an 'ugly' alternative:

SELECT UserId,
       Timeslot,
       SUBSTRING(MAX(CASE Activity WHEN "General" THEN "00General" WHEN "Football" THEN "01Football" ELSE Activity END) , 3)
FROM `Table1`
GROUP BY UserId,
         Timeslot LIMIT 0 ,
                        30

Here we need to define each possible value for Activity.



 MORE:


 ? Mysql UNION with dynamic query
 ? MYSQL: UNION results between two tables where omitting records from first table if PK found in second table
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? Which ORM can preserve the correspondence between db schema and domain model?
 ? how to minimize application downtime when updating database and application ORM
 ? Can we use MongoDB with ORMs we used to use with relational databases, such as linq2sql, entity framework, subsonic,...?
 ? How to use multilanguage database schema with ORM?
 ? Please suggest one .Net ORM, which supports runtime schema evolution