CODEWITHSUNDEEP
mysqldistinctunion
elb98rm
elb98rm

Reputation: 800

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:

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:

Or something like a:

Cheers Rick

Upvotes: 0

Views: 187

Answers (1)

Alepac
Alepac

Reputation: 1831

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.

Upvotes: 0

Related Questions