mysql> SELECT `player`.`role`, `wizard`.`firstname`, `wizard`.`lastname`, `team`.`name` FROM `wizard` LEFT JOIN `player` ON `wizard`.`id` = `player`.`wizard_id` LEFT JOIN `team` ON `team`.`id` = `player`.`team_id` ORDER BY `role`,`name`,`lastname`,`firstname` ASC;
+--------+-------------+-----------------+------------+
| role   | firstname   | lastname        | name       |
+--------+-------------+-----------------+------------+
| NULL   | Crabbe      |                 | NULL       |
| NULL   | Terry       | Boot            | NULL       |
| NULL   | Remus       | Lupin           | NULL       |
| NULL   | Padma       | Patil           | NULL       |
| NULL   | Parvati     | Patil           | NULL       |
| NULL   | Demelza     | Robins          | NULL       |
| NULL   | Horace      | Slughorn        | NULL       |
| NULL   | Charles     | Weasley         | NULL       |
| NULL   | Molly       | Weasley         | NULL       |
| beater | Sirius      | Black           | Gryffindor |
| beater | Lavender    | Brown           | Gryffindor |
| beater | Seamus      | Finnigan        | Gryffindor |
| beater | Rubeus      | Hagrid          | Gryffindor |
| beater | Alice       | Longbottom      | Gryffindor |
| beater | Minerva     | McGonagall      | Gryffindor |
| beater | Harry       | Potter          | Gryffindor |
| beater | James       | Potter          | Gryffindor |
| beater | Dean        | Thomas          | Gryffindor |
| beater | Arthur      | Weasley         | Gryffindor |
| beater | Percy       | Weasley         | Gryffindor |
| beater | Hannah      | Abbott          | Hufflepuff |
| beater | Justin      | Finch-Fletchley | Hufflepuff |
| beater | Fat         | Friar           | Hufflepuff |
| beater | Hepzibah    | Smith           | Hufflepuff |
| beater | Pomona      | Sprout          | Hufflepuff |
| beater | Nymphadora  | Tonks           | Hufflepuff |
| beater | Penelope    | Clearwater      | Ravenclaw  |
| beater | Quirinus    | Quirrell        | Ravenclaw  |
| beater | Helena      | Ravenclaw       | Ravenclaw  |
| beater | Myrtle      | Warren          | Ravenclaw  |
| beater | Millicent   | Bulstrode       | Slytherin  |
| beater | Vincent     | Crabbe          | Slytherin  |
| beater | Marcus      | Flint           | Slytherin  |
| beater | Pansy       | Parkinson       | Slytherin  |
| beater | Severus     | Snape           | Slytherin  |
| beater | Blaise      | Zabini          | Slytherin  |
| chaser | Katie       | Bell            | Gryffindor |
| chaser | Albus       | Dumbledore      | Gryffindor |
| chaser | Hermione    | Granger         | Gryffindor |
| chaser | Lily        | J.              | Gryffindor |
| chaser | Lee         | Jordan          | Gryffindor |
| chaser | Augusta     | Longbottom      | Gryffindor |
| chaser | Frank       | Longbottom      | Gryffindor |
| chaser | Neville     | Longbottom      | Gryffindor |
| chaser | Peter       | Pettigrew       | Gryffindor |
| chaser | Alicia      | Spinnet         | Gryffindor |
| chaser | George      | Weasley         | Gryffindor |
| chaser | Oliver      | Wood            | Gryffindor |
| chaser | Amelia      | Bones           | Hufflepuff |
| chaser | Newton      | Scamander       | Hufflepuff |
| chaser | Cho         | Chang           | Ravenclaw  |
| chaser | Marietta    | Edgecombe       | Ravenclaw  |
| chaser | Filius      | Flitwick        | Ravenclaw  |
| chaser | Anthony     | Goldstein       | Ravenclaw  |
| chaser | Gilderoy    | Lockhart        | Ravenclaw  |
| chaser | Luna        | Lovegood        | Ravenclaw  |
| chaser | Xenophilius | Lovegood        | Ravenclaw  |
| chaser | Garrick     | Ollivander      | Ravenclaw  |
| chaser | Bloody      | Baron           | Slytherin  |
| chaser | Bellatrix   | Lestrange       | Slytherin  |
| chaser | Rodolphus   | Lestrange       | Slytherin  |
| chaser | Draco       | Malfoy          | Slytherin  |
| chaser | Lucius      | Malfoy          | Slytherin  |
| chaser | Phineas     | Nigellus        | Slytherin  |
| chaser | Theodore    | Nott            | Slytherin  |
| chaser | Tom         | Riddle          | Slytherin  |
| chaser | Andromeda   | Tonks           | Slytherin  |
| keeper | Cadogan     |                 | Gryffindor |
| keeper | Dennis      | Creevey         | Gryffindor |
| keeper | Nicholas    | de              | Gryffindor |
| keeper | Aberforth   | Dumbledore      | Gryffindor |
| keeper | Angelina    | Johnson         | Gryffindor |
| keeper | Ginevra     | Weasley         | Gryffindor |
| keeper | Susan       | Bones           | Hufflepuff |
| keeper | Cedric      | Diggory         | Hufflepuff |
| keeper | Zacharias   | Smith           | Hufflepuff |
| keeper | Regulus     | Black           | Slytherin  |
| seeker | Cuthbert    | Binns           | Gryffindor |
| seeker | Colin       | Creevey         | Gryffindor |
| seeker | Godric      | Gryffindor      | Gryffindor |
| seeker | Romilda     | Vane            | Gryffindor |
| seeker | Fred        | Weasley         | Gryffindor |
| seeker | Ronald      | Weasley         | Gryffindor |
| seeker | William     | Weasley         | Gryffindor |
| seeker | Helga       | Hufflepuff      | Hufflepuff |
| seeker | Michael     | Corner          | Ravenclaw  |
| seeker | Rowena      | Ravenclaw       | Ravenclaw  |
| seeker | Sybill      | Trelawney       | Ravenclaw  |
| seeker | Gregory     | Goyle           | Slytherin  |
| seeker | Rabastan    | Lestrange       | Slytherin  |
| seeker | Narcissa    | Malfoy          | Slytherin  |
| seeker | Salazar     | Slytherin       | Slytherin  |
| seeker | Dolores     | Umbridge        | Slytherin  |
+--------+-------------+-----------------+------------+
93 rows in set (0,00 sec)


mysql> SELECT `wizard`.`firstname`, `wizard`.`lastname`, `player`.`role` FROM `wizard` LEFT JOIN `player` ON `wizard`.`id` = `player`.`wizard_id`  WHERE (( `role` = "seeker")) ORDER BY `lastname`, `firstname` ASC;
+-----------+------------+--------+
| firstname | lastname   | role   |
+-----------+------------+--------+
| Cuthbert  | Binns      | seeker |
| Michael   | Corner     | seeker |
| Colin     | Creevey    | seeker |
| Gregory   | Goyle      | seeker |
| Godric    | Gryffindor | seeker |
| Helga     | Hufflepuff | seeker |
| Rabastan  | Lestrange  | seeker |
| Narcissa  | Malfoy     | seeker |
| Rowena    | Ravenclaw  | seeker |
| Salazar   | Slytherin  | seeker |
| Sybill    | Trelawney  | seeker |
| Dolores   | Umbridge   | seeker |
| Romilda   | Vane       | seeker |
| Fred      | Weasley    | seeker |
| Ronald    | Weasley    | seeker |
| William   | Weasley    | seeker |
+-----------+------------+--------+
16 rows in set (0,00 sec)

mysql> SELECT `wizard`.`firstname`, `wizard`.`lastname`, `player`.`role` FROM `wizard` LEFT JOIN `player` ON `wizard`.`id` = `player`.`wizard_id`  WHERE `role` IS NULL;
+-----------+----------+------+
| firstname | lastname | role |
+-----------+----------+------+
| Terry     | Boot     | NULL |
| Crabbe    |          | NULL |
| Remus     | Lupin    | NULL |
| Padma     | Patil    | NULL |
| Parvati   | Patil    | NULL |
| Demelza   | Robins   | NULL |
| Horace    | Slughorn | NULL |
| Charles   | Weasley  | NULL |
| Molly     | Weasley  | NULL |
+-----------+----------+------+
9 rows in set (0,01 sec)

mysql>