API tools faq
paste
Sora952

Jointures Quidich !

Sora952
May 28th, 2020
71
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 10.32 KB | None | 0 0
raw download clone embed print report
  1. select t.name team_name, p.role, w.lastname, w.firstname FROM player p JOIN team t ON t.id=p.team_id JOIN wizard w ON w.id=p.wizard_id ORDER BY t.name;
  2. +------------+--------+-----------------+-------------+
  3. | team_name  | role   | lastname        | firstname   |
  4. +------------+--------+-----------------+-------------+
  5. | Gryffindor | chaser | Bell            | Katie       |
  6. | Gryffindor | seeker | Binns           | Cuthbert    |
  7. | Gryffindor | beater | Black           | Sirius      |
  8. | Gryffindor | beater | Brown           | Lavender    |
  9. | Gryffindor | seeker | Creevey         | Colin       |
  10. | Gryffindor | keeper | Creevey         | Dennis      |
  11. | Gryffindor | keeper | Dumbledore      | Aberforth   |
  12. | Gryffindor | chaser | Dumbledore      | Albus       |
  13. | Gryffindor | beater | Finnigan        | Seamus      |
  14. | Gryffindor | chaser | Granger         | Hermione    |
  15. | Gryffindor | seeker | Gryffindor      | Godric      |
  16. | Gryffindor | beater | Hagrid          | Rubeus      |
  17. | Gryffindor | keeper | Johnson         | Angelina    |
  18. | Gryffindor | chaser | Jordan          | Lee         |
  19. | Gryffindor | beater | Longbottom      | Alice       |
  20. | Gryffindor | chaser | Longbottom      | Frank       |
  21. | Gryffindor | chaser | Longbottom      | Augusta     |
  22. | Gryffindor | chaser | Longbottom      | Neville     |
  23. | Gryffindor | beater | McGonagall      | Minerva     |
  24. | Gryffindor | chaser | Pettigrew       | Peter       |
  25. | Gryffindor | beater | Potter          | Harry       |
  26. | Gryffindor | beater | Potter          | James       |
  27. | Gryffindor | chaser | J.              | Lily        |
  28. | Gryffindor | chaser | Spinnet         | Alicia      |
  29. | Gryffindor | beater | Thomas          | Dean        |
  30. | Gryffindor | seeker | Vane            | Romilda     |
  31. | Gryffindor | beater | Weasley         | Arthur      |
  32. | Gryffindor | seeker | Weasley         | William     |
  33. | Gryffindor | seeker | Weasley         | Fred        |
  34. | Gryffindor | chaser | Weasley         | George      |
  35. | Gryffindor | keeper | Weasley         | Ginevra     |
  36. | Gryffindor | beater | Weasley         | Percy       |
  37. | Gryffindor | seeker | Weasley         | Ronald      |
  38. | Gryffindor | chaser | Wood            | Oliver      |
  39. | Gryffindor | keeper |                 | Cadogan     |
  40. | Gryffindor | keeper | de              | Nicholas    |
  41. | Hufflepuff | beater | Abbott          | Hannah      |
  42. | Hufflepuff | chaser | Bones           | Amelia      |
  43. | Hufflepuff | keeper | Bones           | Susan       |
  44. | Hufflepuff | keeper | Diggory         | Cedric      |
  45. | Hufflepuff | beater | Finch-Fletchley | Justin      |
  46. | Hufflepuff | seeker | Hufflepuff      | Helga       |
  47. | Hufflepuff | chaser | Scamander       | Newton      |
  48. | Hufflepuff | beater | Smith           | Hepzibah    |
  49. | Hufflepuff | keeper | Smith           | Zacharias   |
  50. | Hufflepuff | beater | Sprout          | Pomona      |
  51. | Hufflepuff | beater | Tonks           | Nymphadora  |
  52. | Hufflepuff | beater | Friar           | Fat         |
  53. | Ravenclaw  | chaser | Chang           | Cho         |
  54. | Ravenclaw  | beater | Clearwater      | Penelope    |
  55. | Ravenclaw  | seeker | Corner          | Michael     |
  56. | Ravenclaw  | chaser | Edgecombe       | Marietta    |
  57. | Ravenclaw  | chaser | Flitwick        | Filius      |
  58. | Ravenclaw  | chaser | Goldstein       | Anthony     |
  59. | Ravenclaw  | chaser | Lockhart        | Gilderoy    |
  60. | Ravenclaw  | chaser | Lovegood        | Luna        |
  61. | Ravenclaw  | chaser | Lovegood        | Xenophilius |
  62. | Ravenclaw  | chaser | Ollivander      | Garrick     |
  63. | Ravenclaw  | beater | Quirrell        | Quirinus    |
  64. | Ravenclaw  | beater | Ravenclaw       | Helena      |
  65. | Ravenclaw  | seeker | Ravenclaw       | Rowena      |
  66. | Ravenclaw  | seeker | Trelawney       | Sybill      |
  67. | Ravenclaw  | beater | Warren          | Myrtle      |
  68. | Slytherin  | chaser | Nigellus        | Phineas     |
  69. | Slytherin  | keeper | Black           | Regulus     |
  70. | Slytherin  | beater | Bulstrode       | Millicent   |
  71. | Slytherin  | beater | Crabbe          | Vincent     |
  72. | Slytherin  | beater | Flint           | Marcus      |
  73. | Slytherin  | seeker | Goyle           | Gregory     |
  74. | Slytherin  | chaser | Lestrange       | Bellatrix   |
  75. | Slytherin  | seeker | Lestrange       | Rabastan    |
  76. | Slytherin  | chaser | Lestrange       | Rodolphus   |
  77. | Slytherin  | chaser | Malfoy          | Draco       |
  78. | Slytherin  | chaser | Malfoy          | Lucius      |
  79. | Slytherin  | seeker | Malfoy          | Narcissa    |
  80. | Slytherin  | chaser | Nott            | Theodore    |
  81. | Slytherin  | beater | Parkinson       | Pansy       |
  82. | Slytherin  | chaser | Riddle          | Tom         |
  83. | Slytherin  | seeker | Slytherin       | Salazar     |
  84. | Slytherin  | beater | Snape           | Severus     |
  85. | Slytherin  | chaser | Tonks           | Andromeda   |
  86. | Slytherin  | seeker | Umbridge        | Dolores     |
  87. | Slytherin  | beater | Zabini          | Blaise      |
  88. | Slytherin  | chaser | Baron           | Bloody      |
  89. +------------+--------+-----------------+-------------+
  90. 84 rows in set (0.01 sec)
  91.  
  92. mysql>
  93.  
  94. mysql> select w.lastname, w.firstname FROM player p JOIN wizard w ON w.id=p.wizard_id WHERE p.role='seeker' ORDER BY w.lastname, w.firstname;
  95. +------------+-----------+
  96. | lastname   | firstname |
  97. +------------+-----------+
  98. | Binns      | Cuthbert  |
  99. | Corner     | Michael   |
  100. | Creevey    | Colin     |
  101. | Goyle      | Gregory   |
  102. | Gryffindor | Godric    |
  103. | Hufflepuff | Helga     |
  104. | Lestrange  | Rabastan  |
  105. | Malfoy     | Narcissa  |
  106. | Ravenclaw  | Rowena    |
  107. | Slytherin  | Salazar   |
  108. | Trelawney  | Sybill    |
  109. | Umbridge   | Dolores   |
  110. | Vane       | Romilda   |
  111. | Weasley    | Fred      |
  112. | Weasley    | Ronald    |
  113. | Weasley    | William   |
  114. +------------+-----------+
  115. 16 rows in set (0.00 sec)
  116.  
  117. mysql>
  118.  
  119. // SELECT * FROM wizard LEFT JOIN player ON wizard.id = player.wizard_id WHERE player.wizard_id IS NULL; la commande qui fonctionne, ceux d'en dessous c'est des tests
  120.  
  121. mysql> select w.lastname, w.firstname, p.role FROM player p RIGHT JOIN wizard w ON w.id=p.wizard_id ORDER BY w.lastname, w.firstname;
  122. +-----------------+-------------+--------+
  123. | lastname        | firstname   | role   |
  124. +-----------------+-------------+--------+
  125. |                 | Cadogan     | keeper |
  126. |                 | Crabbe      | NULL   |
  127. | Abbott          | Hannah      | beater |
  128. | Baron           | Bloody      | chaser |
  129. | Bell            | Katie       | chaser |
  130. | Binns           | Cuthbert    | seeker |
  131. | Black           | Regulus     | keeper |
  132. | Black           | Sirius      | beater |
  133. | Bones           | Amelia      | chaser |
  134. | Bones           | Susan       | keeper |
  135. | Boot            | Terry       | NULL   |
  136. | Brown           | Lavender    | beater |
  137. | Bulstrode       | Millicent   | beater |
  138. | Chang           | Cho         | chaser |
  139. | Clearwater      | Penelope    | beater |
  140. | Corner          | Michael     | seeker |
  141. | Crabbe          | Vincent     | beater |
  142. | Creevey         | Colin       | seeker |
  143. | Creevey         | Dennis      | keeper |
  144. | de              | Nicholas    | keeper |
  145. | Diggory         | Cedric      | keeper |
  146. | Dumbledore      | Aberforth   | keeper |
  147. | Dumbledore      | Albus       | chaser |
  148. | Edgecombe       | Marietta    | chaser |
  149. | Finch-Fletchley | Justin      | beater |
  150. | Finnigan        | Seamus      | beater |
  151. | Flint           | Marcus      | beater |
  152. | Flitwick        | Filius      | chaser |
  153. | Friar           | Fat         | beater |
  154. | Goldstein       | Anthony     | chaser |
  155. | Goyle           | Gregory     | seeker |
  156. | Granger         | Hermione    | chaser |
  157. | Gryffindor      | Godric      | seeker |
  158. | Hagrid          | Rubeus      | beater |
  159. | Hufflepuff      | Helga       | seeker |
  160. | J.              | Lily        | chaser |
  161. | Johnson         | Angelina    | keeper |
  162. | Jordan          | Lee         | chaser |
  163. | Lestrange       | Bellatrix   | chaser |
  164. | Lestrange       | Rabastan    | seeker |
  165. | Lestrange       | Rodolphus   | chaser |
  166. | Lockhart        | Gilderoy    | chaser |
  167. | Longbottom      | Alice       | beater |
  168. | Longbottom      | Augusta     | chaser |
  169. | Longbottom      | Frank       | chaser |
  170. | Longbottom      | Neville     | chaser |
  171. | Lovegood        | Luna        | chaser |
  172. | Lovegood        | Xenophilius | chaser |
  173. | Lupin           | Remus       | NULL   |
  174. | Malfoy          | Draco       | chaser |
  175. | Malfoy          | Lucius      | chaser |
  176. | Malfoy          | Narcissa    | seeker |
  177. | McGonagall      | Minerva     | beater |
  178. | Nigellus        | Phineas     | chaser |
  179. | Nott            | Theodore    | chaser |
  180. | Ollivander      | Garrick     | chaser |
  181. | Parkinson       | Pansy       | beater |
  182. | Patil           | Padma       | NULL   |
  183. | Patil           | Parvati     | NULL   |
  184. | Pettigrew       | Peter       | chaser |
  185. | Potter          | Harry       | beater |
  186. | Potter          | James       | beater |
  187. | Quirrell        | Quirinus    | beater |
  188. | Ravenclaw       | Helena      | beater |
  189. | Ravenclaw       | Rowena      | seeker |
  190. | Riddle          | Tom         | chaser |
  191. | Robins          | Demelza     | NULL   |
  192. | Scamander       | Newton      | chaser |
  193. | Slughorn        | Horace      | NULL   |
  194. | Slytherin       | Salazar     | seeker |
  195. | Smith           | Hepzibah    | beater |
  196. | Smith           | Zacharias   | keeper |
  197. | Snape           | Severus     | beater |
  198. | Spinnet         | Alicia      | chaser |
  199. | Sprout          | Pomona      | beater |
  200. | Thomas          | Dean        | beater |
  201. | Tonks           | Andromeda   | chaser |
  202. | Tonks           | Nymphadora  | beater |
  203. | Trelawney       | Sybill      | seeker |
  204. | Umbridge        | Dolores     | seeker |
  205. | Vane            | Romilda     | seeker |
  206. | Warren          | Myrtle      | beater |
  207. | Weasley         | Arthur      | beater |
  208. | Weasley         | Charles     | NULL   |
  209. | Weasley         | Fred        | seeker |
  210. | Weasley         | George      | chaser |
  211. | Weasley         | Ginevra     | keeper |
  212. | Weasley         | Molly       | NULL   |
  213. | Weasley         | Percy       | beater |
  214. | Weasley         | Ronald      | seeker |
  215. | Weasley         | William     | seeker |
  216. | Wood            | Oliver      | chaser |
  217. | Zabini          | Blaise      | beater |
  218. +-----------------+-------------+--------+
  219. 93 rows in set (0.01 sec)
  220.  
  221. mysql> select w.lastname, w.firstname, p.role FROM player p RIGHT JOIN wizard w ON w.id=p.wizard_id WHERE p.role=NULL ORDER BY w.lastname, w.firstname;
  222. Empty set (0.00 sec)
  223.  
  224. mysql> SELECT * FROM wizard LEFT JOIN player ON wizard.id = player.wizard_id  WHERE player.wizard_id is NULL;
  225. +----+-----------+----------+------+-----------+---------+------+-----------------+
  226. | id | firstname | lastname | id   | wizard_id | team_id | role | enrollment_date |
  227. +----+-----------+----------+------+-----------+---------+------+-----------------+
  228. |  9 | Terry     | Boot     | NULL |      NULL |    NULL | NULL | NULL            |
  229. | 15 | Crabbe    |          | NULL |      NULL |    NULL | NULL | NULL            |
  230. | 45 | Remus     | Lupin    | NULL |      NULL |    NULL | NULL | NULL            |
  231. | 53 | Padma     | Patil    | NULL |      NULL |    NULL | NULL | NULL            |
  232. | 54 | Parvati   | Patil    | NULL |      NULL |    NULL | NULL | NULL            |
  233. | 63 | Demelza   | Robins   | NULL |      NULL |    NULL | NULL | NULL            |
  234. | 65 | Horace    | Slughorn | NULL |      NULL |    NULL | NULL | NULL            |
  235. | 80 | Charles   | Weasley  | NULL |      NULL |    NULL | NULL | NULL            |
  236. | 84 | Molly     | Weasley  | NULL |      NULL |    NULL | NULL | NULL            |
  237. +----+-----------+----------+------+-----------+---------+------+-----------------+
  238. 9 rows in set (0.00 sec)
  239.  
  240. mysql>
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!