MySQL FULL JOIN one table, and LEFT JOIN another in the same query?

Question

I have three tables that I would like to link in this single query.

script is a register of attendance, so it registers a sign of attendance for each meeting, for each user.

Three tables used:

"team":

id | fullname | position         | class | hidden
1  | Team     | --               | black | 1
2  | Dan S    | Team Manager     | green | 0
3  | Harry P  | Graphic Engineer | blue  | 0

"Register":

id | mid | uid | mark
1  | 1   | 2   | /
2  | 1   | 3   | I
3  | 2   | 1   | /
4  | 2   | 3   | /

"a meeting":

id | maintask            | starttime  | endtime
1  | Organise Year Ahead | 1330007400 | 1330012800
2  | Gather Ideas        | 1330612200 | 1330617600
3  | TODO                | 1331217000 | 1331222400

There is sample data. I want to do this:

Select all the results from the register, group them by the user and order them according to the start time of the meeting. But, if there is no label in the register table, I want it to display "-" (can be done with php if necessary). Thus, the expected result looks like this:

fullname | mark | mid
Dan S    | /    | 1
Dan S    | /    | 2
Dan S    | -    | 3
Harry P  | I    | 1
Harry P  | /    | 2
Harry P  | -    | 3

My SQL query is currently:

SELECT u. fullname, u. id, r. mark, r. mid FROM team u FULL JOIN register r ON r. uid= u. id LEFT JOIN meetings m ON r. mid= m. id GROUP BY u. id ORDER BY m. starttime ASC

MySQL:

SQL; , MySQL 'FULL JOIN register r ON r. uid= u. id LEFT JOIN meetings m ON r. mid= m.`id ' 1

: S

, - , .

Dan

: , :

   SELECT     
       u.fullname,    u.id as uid,    
       if(r.uid = u.id, r.mark, '-') as mark,    
       if(r.uid = u.id, r.mid, '-') as mid,    
       r.mid,    m.starttime 
   FROM     
       team u 
         CROSS JOIN     
       register r ON u.id = r.uid
         LEFT OUTER JOIN     
       meetings m ON r.mid = m.id  
   WHERE    
       u.hidden = 0 
   GROUP BY     
       u.id, r.mid 
   ORDER BY     
       m.starttime, u.id ASC

Answer 1

Full outer join MySQL. , 5.6 MySQL Join doc. A cross join :

EDITED

SELECT 
   UxM.fullname,
   r.mark,
   UxM.mid,
   UxM.starttime
FROM 
   ( select u.id as uid, m.id as mid, u.fullname, m.starttime
     from
     team  u
      CROSS JOIN   
     meetings ) UxM
   left join
   register r
      on UxM.uid = r.uid and UxM.mid = r.mid
ORDER BY 
   UxM.starttime ASC

, .

---

:

SELECT     
    u.fullname,
    u.id AS uid,    
    COALESCE(r.mark, '-') AS mark,    
    COALESCE(r.mid, '-') AS mid,    
    m.id,
    m.starttime 
FROM     
    team u 
      CROSS JOIN     
    meetings m 
      LEFT JOIN     
    register r
        ON   r.mid = m.id  
        AND  r.id = u.uid
WHERE    
    u.hidden = 0 
GROUP BY     
    m.id, u.id 
ORDER BY     
    m.starttime, u.id