Select query help

[JayJ]

Hi,

I require some help in making the following possible with mySQL.

My [simplified] database layout is as follows

UserID
Timestamp
League
Field 1
Field 2
Field 3
Field 4
Field 5
Field 6

Fields 1-6 values are either "yes" or "no"

Basically a user submits a form selecting the League they wish to update. They tick the checkboxes to state whether something has been done or not.

e.g.

UserID, Timestamp, League 1, Yes, No, No, No, Yes, No

Another user can then submit a form with what they have done but it may read:

UserID, Timestamp, League 1, No, Yes, Yes, No, No, No

Obviously, from those 2 submissions I can see that Fields 1, 2, 3 and 5 have all had a 'Yes' value stating those items have been completed. Fields 4 and 6 have on both occassions not been done (and reads "no").

User 3 may then come along and update League 2 which has no impact on League 1.

What I am looking for is a mySQL query that outputs the rows as a collective with "Yes" for each League (League 1, League 2, League 3, League 4) so the query would come back with one string:

League 1, Yes, Yes, Yes, No, Yes, No

League 2, No, No, No, No, No, No

Any assistance would be greatly appreciated

Kind Regards

JJ

[griswolf]

If you don't need to keep track of the history of the changes, then if you merely UPDATE the row instead of adding a new row each time, your problem is solved.

To keep a history, you can store a "running total" so that your "view" query only looks at the latest row (order by timestamp desc limit 1)

[JayJ]

The issue there would be that an UPDATE query would overwrite all fields? As such if organiser 1 was to update Yes Yes No No No Yes and organiser 2 was to update with all No - then it would update all to No?

Keeping track of the changes was more of an administration thing to keep track of who is doing what and who has applied which change.

[smantscheff]

Maybe you mean something like this:

drop table leagues;
create table leagues (id integer, f1 enum('no', 'yes'), f2 enum('no','yes'));
insert into leagues values 
(1,'no','no'),
(1,'yes','no'),
(2,'no','no');
select id, 
if(sum(if(f1 = 'no', 0, 1)) = 0, 'no', 'yes') as field1, 
if(sum(if(f2 = 'no', 0, 1)) = 0, 'no', 'yes') as field2
from leagues
group by id;

+------+--------+--------+
| id   | field1 | field2 |
+------+--------+--------+
|    1 | yes    | no     |
|    2 | no     | no     |
+------+--------+--------+

[JayJ]

That could be it, smantscheff - I will try that tomorrow and will update you accordingly :)

[JayJ]

Thankyou smantscheff :) Exactly what I was looking for in the end