mysql joining table

Question

I am new with joining tables.

ClassList
========================
ID      Level
1       2
2       2
3       4
4       1
5       3
6       7

BoysList
========================
ID      Name        Age
1       Alex        13
2       Arnold      16
3       Barny       15

GirlsList
========================
ID      Name        Age
4       Arnie       12
5       Martha      17
6       Beth        15

I was able to join two tables. But, what if, in this situation, you have to combine first the two tables (BoysList and GirlsList) and then join it with ClassList.

The Resultset would be:

Result
================================================
ID      Name
4       Arnie           
6       Beth        
5       Martha      
1       Alex    
2       Arnold  
3       Barny       

order of the result would be list of boys girls first, name ASC. How do accomplish this?

Answer 1

the first thing you will do is to UNION the BoysList and GirlsList then INNER JOIN it with ClassList

SELECT  iStudentList.`ID`,
    iStudentList.`Name`,
    iStudentList.`Age`,
    ClassList.`iOrder`
FROM
ClassList INNER JOIN 
    (SELECT `ID`, `Name`, `Age`, "A" as iOrder FROM BoyssList
        UNION
     SELECT `ID`, `Name`, `Age`, "B" as iOrder FROM GirlsList) as iStudentList
ON ClassList.`ID` = iStudentList.`ID`
ORDER BY ClassList.`iOrder` DESC, iStudentList.`Name` ASC