mysql - Using SQL Union to perform complex report query that depends on increasingly more relaxed conditions -

i have following schema (part of pertains question):

# schema - table * table field  # schema     - mods         * id         * title         * affiliation         * attended     - modusergrades         * modid          * userid         * questionvalues         * grade         * accepted         * comments     - users         * id         * projects         * location         * grade          * termid      -         * email         * firstname         * lastname         * userid         * modid         * termid 

once mods have registered event, able attend event (if log in during time, attended set 1). there, able grade number of users, maxusers. if have graded (questionvalues != null && grade != null) less maxusers, have started grading, if have graded maxusers have finished grading.

now, want able differentiate between following classes of mods:

• mods have registered, did not attend (i.e. attended = 0)
• mods have attended event did not start grading (attended = 1 questionvalues == null && grade == null)
• mods have started grading did not finish (so, have non-null values above 2 fields less maxusers)
• mods have finished grading (== maxusers).

i want able sql query puts of information in 1 place.

first off, would union operator fitting choice task? if not, how might approach problem?

second, , if answer previous question yes, here have far mods have graded. right approach?

select  m.id, m.title, m.attended, a.firstname, a.lastname, a.email, a.termid  inner join mods m on m.id = a.modid m.id = a.modid      , attended = 1      , (select count(*) modusergrade mug          m.id = mug.modid              , questionvalues not null              , grade not null              , accepted = 1) > 5  union  select m.id, m.title, 'graded' attended, a.firstname, a.lastname, a.email, a.termid inner join mods m on m.id = a.modid m.id = a.modid      , attended = 1  

edit: make question clearer, here aim:

graded subset of started grading, subset of attended, subset of registered. set notation:

graded ⊆ started grading ⊆ attended ⊆ registered.

is possible create sql union query captures behaviour? need else?

you not need union query this. need case statement has hierarchical logic looking for. requires information all , mug tables along summarized information modusergrades.

i don't see max questions value in tables, assume sort of parameter.

the query want like:

select m.id, m.title, m.attended, a.firstname, a.lastname, a.email, a.termid,        (case when m.attended = 0 'registered'              when mug.modid null 'attended'              when mug.numgraded < $maxcnt 'started'              when mug.numgraded = $maxcnt 'finished'         end) status inner join      mods m      on m.id = a.modid left join      (select modid, count(*) numgraded       modusergrades mug       questionvalues not null , grade not null       group modid      ) mug      on mug.modid = m.id