Activity 2 - SELECT JOIN.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Activity 2 - SELECT JOIN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "db = sqlite3.connect('database.sqlite')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Basic Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = db.execute(\"\"\"\n",
    "    SELECT * FROM League INNER JOIN Country\n",
    "    ON League.country_id = Country.id\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 1, 'Belgium Jupiler League', 1, 'Belgium'),\n",
       " (1729, 1729, 'England Premier League', 1729, 'England'),\n",
       " (4769, 4769, 'France Ligue 1', 4769, 'France'),\n",
       " (7809, 7809, 'Germany 1. Bundesliga', 7809, 'Germany'),\n",
       " (10257, 10257, 'Italy Serie A', 10257, 'Italy'),\n",
       " (13274, 13274, 'Netherlands Eredivisie', 13274, 'Netherlands'),\n",
       " (15722, 15722, 'Poland Ekstraklasa', 15722, 'Poland'),\n",
       " (17642, 17642, 'Portugal Liga ZON Sagres', 17642, 'Portugal'),\n",
       " (19694, 19694, 'Scotland Premier League', 19694, 'Scotland'),\n",
       " (21518, 21518, 'Spain LIGA BBVA', 21518, 'Spain'),\n",
       " (24558, 24558, 'Switzerland Super League', 24558, 'Switzerland')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = db.execute(\"\"\"\n",
    "    SELECT League.name, Country.name FROM League INNER JOIN Country\n",
    "    ON League.country_id = Country.id\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Belgium Jupiler League', 'Belgium'),\n",
       " ('England Premier League', 'England'),\n",
       " ('France Ligue 1', 'France'),\n",
       " ('Germany 1. Bundesliga', 'Germany'),\n",
       " ('Italy Serie A', 'Italy'),\n",
       " ('Netherlands Eredivisie', 'Netherlands'),\n",
       " ('Poland Ekstraklasa', 'Poland'),\n",
       " ('Portugal Liga ZON Sagres', 'Portugal'),\n",
       " ('Scotland Premier League', 'Scotland'),\n",
       " ('Spain LIGA BBVA', 'Spain'),\n",
       " ('Switzerland Super League', 'Switzerland')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Aliases**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = db.execute(\"\"\"\n",
    "    SELECT L.name, C.name FROM League L INNER JOIN Country C\n",
    "    ON L.country_id = C.id\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Belgium Jupiler League', 'Belgium'),\n",
       " ('England Premier League', 'England'),\n",
       " ('France Ligue 1', 'France'),\n",
       " ('Germany 1. Bundesliga', 'Germany'),\n",
       " ('Italy Serie A', 'Italy'),\n",
       " ('Netherlands Eredivisie', 'Netherlands'),\n",
       " ('Poland Ekstraklasa', 'Poland'),\n",
       " ('Portugal Liga ZON Sagres', 'Portugal'),\n",
       " ('Scotland Premier League', 'Scotland'),\n",
       " ('Spain LIGA BBVA', 'Spain'),\n",
       " ('Switzerland Super League', 'Switzerland')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prettier Output using Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Belgium Jupiler League</td>\n",
       "      <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>England Premier League</td>\n",
       "      <td>England</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>France Ligue 1</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Germany 1. Bundesliga</td>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Italy Serie A</td>\n",
       "      <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Netherlands Eredivisie</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Poland Ekstraklasa</td>\n",
       "      <td>Poland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Portugal Liga ZON Sagres</td>\n",
       "      <td>Portugal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Scotland Premier League</td>\n",
       "      <td>Scotland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Spain LIGA BBVA</td>\n",
       "      <td>Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Switzerland Super League</td>\n",
       "      <td>Switzerland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        name         name\n",
       "0     Belgium Jupiler League      Belgium\n",
       "1     England Premier League      England\n",
       "2             France Ligue 1       France\n",
       "3      Germany 1. Bundesliga      Germany\n",
       "4              Italy Serie A        Italy\n",
       "5     Netherlands Eredivisie  Netherlands\n",
       "6         Poland Ekstraklasa       Poland\n",
       "7   Portugal Liga ZON Sagres     Portugal\n",
       "8    Scotland Premier League     Scotland\n",
       "9            Spain LIGA BBVA        Spain\n",
       "10  Switzerland Super League  Switzerland"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"\"\"\n",
    "    SELECT L.name, C.name FROM League L INNER JOIN Country C\n",
    "    ON L.country_id = C.id\n",
    "\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll use \"Real Madrid\" in the following queries. Just in case, this is its basic info:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>team_api_id</th>\n",
       "      <th>team_fifa_api_id</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>team_short_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43040</td>\n",
       "      <td>8633</td>\n",
       "      <td>243</td>\n",
       "      <td>Real Madrid CF</td>\n",
       "      <td>REA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  team_api_id  team_fifa_api_id  team_long_name team_short_name\n",
       "0  43040         8633               243  Real Madrid CF             REA"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT * FROM Team WHERE team_long_name = \"Real Madrid CF\"', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Advanced Join\n",
    "_(3 or more tables)_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The first match played by Real Madrid at home**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>name</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>away_team_api_id</th>\n",
       "      <th>home_team_goal</th>\n",
       "      <th>away_team_goal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Spain LIGA BBVA</td>\n",
       "      <td>21534</td>\n",
       "      <td>2008-11-08 00:00:00</td>\n",
       "      <td>Real Madrid CF</td>\n",
       "      <td>9864</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name             name     id                 date  team_long_name  \\\n",
       "0  Spain  Spain LIGA BBVA  21534  2008-11-08 00:00:00  Real Madrid CF   \n",
       "\n",
       "   away_team_api_id  home_team_goal  away_team_goal  \n",
       "0              9864               4               3  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"\"\"\n",
    "    SELECT\n",
    "        C.name, L.name, M.id, M.date, T.team_long_name, M.away_team_api_id,\n",
    "        M.home_team_goal, M.away_team_goal\n",
    "    FROM Match M\n",
    "    INNER JOIN Country C ON (M.country_id = C.id)\n",
    "    INNER JOIN League L ON (M.league_id = L.id)\n",
    "    INNER JOIN Team T ON (M.home_team_api_id = T.team_api_id)\n",
    "    WHERE T.team_long_name = \"Real Madrid CF\"\n",
    "    LIMIT 1\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Seems like they won 4-3. Who's the away team?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>team_api_id</th>\n",
       "      <th>team_fifa_api_id</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>team_short_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43054</td>\n",
       "      <td>9864</td>\n",
       "      <td>573</td>\n",
       "      <td>Málaga CF</td>\n",
       "      <td>MAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  team_api_id  team_fifa_api_id team_long_name team_short_name\n",
       "0  43054         9864               573      Málaga CF             MAL"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"\"\"\n",
    "    SELECT *\n",
    "    FROM Team T\n",
    "    WHERE T.team_api_id = 9864\n",
    "    LIMIT 1\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![image](https://user-images.githubusercontent.com/872296/38050073-40be91a8-32a0-11e8-9726-8fac86cccac5.png)\n",
    "\n",
    "This 2008 page seems to confirm it: https://resultados.as.com/resultados/futbol/primera/2008_2009/directo/regular_a_10_13538"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Could we have displayed both team names with only one query?**\n",
    "\n",
    "Yes, you can join many times the same table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>name</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>home_team_goal</th>\n",
       "      <th>away_team_goal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Spain LIGA BBVA</td>\n",
       "      <td>21534</td>\n",
       "      <td>2008-11-08 00:00:00</td>\n",
       "      <td>Real Madrid CF</td>\n",
       "      <td>Málaga CF</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name             name     id                 date  team_long_name  \\\n",
       "0  Spain  Spain LIGA BBVA  21534  2008-11-08 00:00:00  Real Madrid CF   \n",
       "\n",
       "  team_long_name  home_team_goal  away_team_goal  \n",
       "0      Málaga CF               4               3  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"\"\"\n",
    "    SELECT\n",
    "        C.name, L.name, M.id, M.date, T_Home.team_long_name, T_Away.team_long_name,\n",
    "        M.home_team_goal, M.away_team_goal\n",
    "    FROM Match M\n",
    "    INNER JOIN Country C ON (M.country_id = C.id)\n",
    "    INNER JOIN League L ON (M.league_id = L.id)\n",
    "    INNER JOIN Team T_Home ON (M.home_team_api_id = T_Home.team_api_id)\n",
    "    INNER JOIN Team T_Away ON (M.away_team_api_id = T_Away.team_api_id)\n",
    "    WHERE T_Home.id = 43040\n",
    "    LIMIT 1\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we could start also getting the initial team lineup:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>home_team_goal</th>\n",
       "      <th>away_team_goal</th>\n",
       "      <th>H1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21534</td>\n",
       "      <td>2008-11-08 00:00:00</td>\n",
       "      <td>Real Madrid CF</td>\n",
       "      <td>Málaga CF</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Iker Casillas</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                 date  team_long_name team_long_name  home_team_goal  \\\n",
       "0  21534  2008-11-08 00:00:00  Real Madrid CF      Málaga CF               4   \n",
       "\n",
       "   away_team_goal             H1  \n",
       "0               3  Iker Casillas  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"\"\"\n",
    "    SELECT\n",
    "        M.id, M.date, T_Home.team_long_name, T_Away.team_long_name,\n",
    "        M.home_team_goal, M.away_team_goal,\n",
    "        P_Home_1.player_name 'H1'\n",
    "    FROM Match M\n",
    "    INNER JOIN Country C ON (M.country_id = C.id)\n",
    "    INNER JOIN League L ON (M.league_id = L.id)\n",
    "    INNER JOIN Team T_Home ON (M.home_team_api_id = T_Home.team_api_id)\n",
    "    INNER JOIN Team T_Away ON (M.away_team_api_id = T_Away.team_api_id)\n",
    "    \n",
    "    INNER JOIN Player P_Home_1 ON (M.home_player_1 = P_Home_1.player_api_id)\n",
    "    \n",
    "    WHERE T_Home.id = 43040\n",
    "    LIMIT 1\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "According to [the report from 2008](https://resultados.as.com/resultados/futbol/primera/2008_2009/directo/regular_a_10_13538), seems like Iker was Real Madrid's Goalkeeper.\n",
    "\n",
    "We could keep extending it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>home_team_goal</th>\n",
       "      <th>away_team_goal</th>\n",
       "      <th>H1</th>\n",
       "      <th>H2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21534</td>\n",
       "      <td>2008-11-08 00:00:00</td>\n",
       "      <td>Real Madrid CF</td>\n",
       "      <td>Málaga CF</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Iker Casillas</td>\n",
       "      <td>Sergio Ramos</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                 date  team_long_name team_long_name  home_team_goal  \\\n",
       "0  21534  2008-11-08 00:00:00  Real Madrid CF      Málaga CF               4   \n",
       "\n",
       "   away_team_goal             H1            H2  \n",
       "0               3  Iker Casillas  Sergio Ramos  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"\"\"\n",
    "    SELECT\n",
    "        M.id, M.date, T_Home.team_long_name, T_Away.team_long_name,\n",
    "        M.home_team_goal, M.away_team_goal,\n",
    "        P_Home_1.player_name 'H1',\n",
    "        P_Home_2.player_name 'H2'\n",
    "    FROM Match M\n",
    "    INNER JOIN Country C ON (M.country_id = C.id)\n",
    "    INNER JOIN League L ON (M.league_id = L.id)\n",
    "    INNER JOIN Team T_Home ON (M.home_team_api_id = T_Home.team_api_id)\n",
    "    INNER JOIN Team T_Away ON (M.away_team_api_id = T_Away.team_api_id)\n",
    "    \n",
    "    INNER JOIN Player P_Home_1 ON (M.home_player_1 = P_Home_1.player_api_id)\n",
    "    INNER JOIN Player P_Home_2 ON (M.home_player_2 = P_Home_2.player_api_id)\n",
    "    \n",
    "    WHERE T_Home.id = 43040\n",
    "    LIMIT 1\"\"\", db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**But we hate repetition, so we can make Python do the hard work:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_query = \"\"\"\n",
    "    SELECT\n",
    "        M.id, M.date, T_Home.team_long_name, T_Away.team_long_name,\n",
    "        M.home_team_goal, M.away_team_goal,\n",
    "        \n",
    "        {player_columns}\n",
    "        \n",
    "    FROM Match M\n",
    "    INNER JOIN Country C ON (M.country_id = C.id)\n",
    "    INNER JOIN League L ON (M.league_id = L.id)\n",
    "    INNER JOIN Team T_Home ON (M.home_team_api_id = T_Home.team_api_id)\n",
    "    INNER JOIN Team T_Away ON (M.away_team_api_id = T_Away.team_api_id)\n",
    "    \n",
    "    {player_joins}\n",
    "    \n",
    "    WHERE T_Home.id = 43040\n",
    "    LIMIT 1\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = \"\"\n",
    "joins = \"\"\n",
    "for i in range(1, 12):\n",
    "    joins += ('INNER JOIN Player P_Home_{pos} ON '\n",
    "              '(M.home_player_{pos} = P_Home_{pos}.player_api_id)\\n').format(pos=i)\n",
    "    columns += \"P_Home_{pos}.player_name 'H{pos}',\\n\".format(pos=i)\n",
    "columns = columns.rstrip(',\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INNER JOIN Player P_Home_1 ON (M.home_player_1 = P_Home_1.player_api_id)\n",
      "INNER JOIN Player P_Home_2 ON (M.home_player_2 = P_Home_2.player_api_id)\n",
      "INNER JOIN Player P_Home_3 ON (M.home_player_3 = P_Home_3.player_api_id)\n",
      "INNER JOIN Player P_Home_4 ON (M.home_player_4 = P_Home_4.player_api_id)\n",
      "INNER JOIN Player P_Home_5 ON (M.home_player_5 = P_Home_5.player_api_id)\n",
      "INNER JOIN Player P_Home_6 ON (M.home_player_6 = P_Home_6.player_api_id)\n",
      "INNER JOIN Player P_Home_7 ON (M.home_player_7 = P_Home_7.player_api_id)\n",
      "INNER JOIN Player P_Home_8 ON (M.home_player_8 = P_Home_8.player_api_id)\n",
      "INNER JOIN Player P_Home_9 ON (M.home_player_9 = P_Home_9.player_api_id)\n",
      "INNER JOIN Player P_Home_10 ON (M.home_player_10 = P_Home_10.player_api_id)\n",
      "INNER JOIN Player P_Home_11 ON (M.home_player_11 = P_Home_11.player_api_id)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(joins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "P_Home_1.player_name 'H1',\n",
      "P_Home_2.player_name 'H2',\n",
      "P_Home_3.player_name 'H3',\n",
      "P_Home_4.player_name 'H4',\n",
      "P_Home_5.player_name 'H5',\n",
      "P_Home_6.player_name 'H6',\n",
      "P_Home_7.player_name 'H7',\n",
      "P_Home_8.player_name 'H8',\n",
      "P_Home_9.player_name 'H9',\n",
      "P_Home_10.player_name 'H10',\n",
      "P_Home_11.player_name 'H11'\n"
     ]
    }
   ],
   "source": [
    "print(columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = base_query.format(player_joins=joins, player_columns=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SELECT\n",
      "        M.id, M.date, T_Home.team_long_name, T_Away.team_long_name,\n",
      "        M.home_team_goal, M.away_team_goal,\n",
      "        \n",
      "        P_Home_1.player_name 'H1',\n",
      "P_Home_2.player_name 'H2',\n",
      "P_Home_3.player_name 'H3',\n",
      "P_Home_4.player_name 'H4',\n",
      "P_Home_5.player_name 'H5',\n",
      "P_Home_6.player_name 'H6',\n",
      "P_Home_7.player_name 'H7',\n",
      "P_Home_8.player_name 'H8',\n",
      "P_Home_9.player_name 'H9',\n",
      "P_Home_10.player_name 'H10',\n",
      "P_Home_11.player_name 'H11'\n",
      "        \n",
      "    FROM Match M\n",
      "    INNER JOIN Country C ON (M.country_id = C.id)\n",
      "    INNER JOIN League L ON (M.league_id = L.id)\n",
      "    INNER JOIN Team T_Home ON (M.home_team_api_id = T_Home.team_api_id)\n",
      "    INNER JOIN Team T_Away ON (M.away_team_api_id = T_Away.team_api_id)\n",
      "    \n",
      "    INNER JOIN Player P_Home_1 ON (M.home_player_1 = P_Home_1.player_api_id)\n",
      "INNER JOIN Player P_Home_2 ON (M.home_player_2 = P_Home_2.player_api_id)\n",
      "INNER JOIN Player P_Home_3 ON (M.home_player_3 = P_Home_3.player_api_id)\n",
      "INNER JOIN Player P_Home_4 ON (M.home_player_4 = P_Home_4.player_api_id)\n",
      "INNER JOIN Player P_Home_5 ON (M.home_player_5 = P_Home_5.player_api_id)\n",
      "INNER JOIN Player P_Home_6 ON (M.home_player_6 = P_Home_6.player_api_id)\n",
      "INNER JOIN Player P_Home_7 ON (M.home_player_7 = P_Home_7.player_api_id)\n",
      "INNER JOIN Player P_Home_8 ON (M.home_player_8 = P_Home_8.player_api_id)\n",
      "INNER JOIN Player P_Home_9 ON (M.home_player_9 = P_Home_9.player_api_id)\n",
      "INNER JOIN Player P_Home_10 ON (M.home_player_10 = P_Home_10.player_api_id)\n",
      "INNER JOIN Player P_Home_11 ON (M.home_player_11 = P_Home_11.player_api_id)\n",
      "\n",
      "    \n",
      "    WHERE T_Home.id = 43040\n",
      "    LIMIT 1\n"
     ]
    }
   ],
   "source": [
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>team_long_name</th>\n",
       "      <th>home_team_goal</th>\n",
       "      <th>away_team_goal</th>\n",
       "      <th>H1</th>\n",
       "      <th>H2</th>\n",
       "      <th>H3</th>\n",
       "      <th>H4</th>\n",
       "      <th>H5</th>\n",
       "      <th>H6</th>\n",
       "      <th>H7</th>\n",
       "      <th>H8</th>\n",
       "      <th>H9</th>\n",
       "      <th>H10</th>\n",
       "      <th>H11</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21534</td>\n",
       "      <td>2008-11-08 00:00:00</td>\n",
       "      <td>Real Madrid CF</td>\n",
       "      <td>Málaga CF</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Iker Casillas</td>\n",
       "      <td>Sergio Ramos</td>\n",
       "      <td>Fabio Cannavaro</td>\n",
       "      <td>Gabriel Heinze</td>\n",
       "      <td>Marcelo</td>\n",
       "      <td>Fernando Gago</td>\n",
       "      <td>Jose Maria Guti</td>\n",
       "      <td>Rafael van der Vaart</td>\n",
       "      <td>Wesley Sneijder</td>\n",
       "      <td>Royston Drenthe</td>\n",
       "      <td>Gonzalo Higuain</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                 date  team_long_name team_long_name  home_team_goal  \\\n",
       "0  21534  2008-11-08 00:00:00  Real Madrid CF      Málaga CF               4   \n",
       "\n",
       "   away_team_goal             H1            H2               H3  \\\n",
       "0               3  Iker Casillas  Sergio Ramos  Fabio Cannavaro   \n",
       "\n",
       "               H4       H5             H6               H7  \\\n",
       "0  Gabriel Heinze  Marcelo  Fernando Gago  Jose Maria Guti   \n",
       "\n",
       "                     H8               H9              H10              H11  \n",
       "0  Rafael van der Vaart  Wesley Sneijder  Royston Drenthe  Gonzalo Higuain  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(query, db)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}