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".