Section 04 Joining Tables/15 MySQL Joins.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "934a488f-8665-4865-9cad-0b2024beeed7",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL Joins](https://www.mysqltutorial.org/mysql-join/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e1e034e-be41-489a-90eb-bcad53ae90b6",
   "metadata": {},
   "source": [
    "Various MySQL join clauses in the SELECT statement to query data from two tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9a8413fe-2a6f-4e69-927d-1cd54f485319",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7c911281-83a5-45ef-9c3c-a4c8aaa1594d",
   "metadata": {},
   "outputs": [],
   "source": [
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b62bb872-2813-4c67-ae49-3f4f969a8c65",
   "metadata": {},
   "source": [
    "### Introduction to MySQL join clauses"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef184ed9-c36a-482e-b89d-3d6f6d7c0a1f",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Setting up sample tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a93693c9-f64f-4a1e-bf29-9a5240a5c566",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_12536/2641228304.py:2: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to \"sqlalchemy<2.0\". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n",
      "  engine.execute('DROP TABLE IF EXISTS members, committees;')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f61de3e51f0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create members and committees tables\n",
    "engine.execute('DROP TABLE IF EXISTS members, committees;')\n",
    "\n",
    "string = '''\n",
    "CREATE TABLE members (\n",
    "    member_id INT AUTO_INCREMENT,\n",
    "    name VARCHAR(100),\n",
    "    PRIMARY KEY (member_id))\n",
    ";'''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "string = '''\n",
    "CREATE TABLE committees(\n",
    "    committee_id INT AUTO_INCREMENT,\n",
    "    name VARCHAR(100),\n",
    "    PRIMARY KEY (committee_id))\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "59b232cc-80f1-43b9-834d-f26f430a0e22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f61de3e5a00>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# insert values into the tables\n",
    "string = '''\n",
    "INSERT INTO members(name)\n",
    "VALUES ('John'), ('Jane'), ('Mary'), ('David'), ('Amelia')\n",
    ";'''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "string = '''\n",
    "INSERT INTO committees(name)\n",
    "VALUES ('John'), ('Mary'), ('Amelia'), ('Joe')\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "daa28669-c21c-4b34-a4b7-6e7ebc17bece",
   "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>member_id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>David</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Amelia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id    name\n",
       "0          1    John\n",
       "1          2    Jane\n",
       "2          3    Mary\n",
       "3          4   David\n",
       "4          5  Amelia"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT * FROM members;', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "17651f57-f037-445f-8f0c-69c9df7fda05",
   "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>committee_id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Amelia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Joe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   committee_id    name\n",
       "0             1    John\n",
       "1             2    Mary\n",
       "2             3  Amelia\n",
       "3             4     Joe"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT * FROM committees;', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34554786-a556-4c27-8398-6b9d70f7a0b5",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL INNER JOIN clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfba5545-4a93-4c14-ac52-83fea4db4559",
   "metadata": {},
   "source": [
    "syntax:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "62d4cc77-d9e8-45be-860f-ba59ca3a1c96",
   "metadata": {},
   "source": [
    "SELECT column_list\n",
    "FROM table_1\n",
    "INNER JOIN table_2 ON join_condition;"
   ]
  },
  {
   "cell_type": "raw",
   "id": "910f3bb9-f37b-4d1a-8c73-7592c8cd0c28",
   "metadata": {},
   "source": [
    "SELECT column_list\n",
    "FROM table_1\n",
    "INNER JOIN table_2 USING (column_name);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8e235ce0-d4b3-4c1c-9bcd-757c854d1d8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYkAAADrCAYAAACclmpYAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH4wgcBhoDWxK3uQAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAgAElEQVR4nO3dfVRU570v8O/e88YwDBCjYPAFAsgYYzRNjOYFxCbeYExMT3NixZ7VNmnXSoT2YtY9vXK7Sl/SQ29OvG1X5a6jJj09se1ZBWtOmgarkquJikmjSVrfFQQDCApoFZgZ5mXP7H3/GGcCyCgvM7OfvZ/fZy2XgLw8g9/9/PbzsvcWFEVRQAghhIxCVLsBhBBC2EVFghBCSFRUJAghhERFRYIQQkhUVCQIIYRERUWCEEJIVFQkCCGEREVFghBCSFRUJAghhERFRYIQQkhUVCQIIYRERUWCEEJIVFQkCCGEREVFghBCSFRUJAghhERFRYIQQkhUVCQIIYRERUWCEEJIVFQkCCGEREVFghBCSFRUJAghhERlVLsBJLYGg0BTfxDNThnNziAG/AqcAQVOSYErADil0NvOwPD37SYBdpOAFCNgNwrD3zcJsBsFpJoFFNgNKLCLcKQZkGxQ+9WSaCgHJFYERVEUtRtBxq/NJV/vAGQ09Qcib3d75OsHrxEFdhHpJkQOdLtJQIoh/L4Y+ZhNBNzykI5DkkOdSXDoxxT0SUDzQKjTaRoIYrpVRIFdHPbzCuwiclJogJoolAMSb1QkNOJUv4z9PRIO9ARwoDcIiwgUpIqRs7nwgZlnT9xp3XmXjKaB4PUO4/O3fTJQnGFAcaYRyzJNuDuNOotYoRyQRKMiwagW5/XOoDeIAz0BmEVg2XRT6KDLMGK2jd0DrsMt40BvqBPb3y3BLwPFmUYUZxiwLNOEfDu7bWcN5YCojYoEQ/Z1B7C9XcL7PRK8QQXLMk0ozjCiONOIORo+oM45Q53F/u5QZ5dkAL6YacKabBMem07LYiNRDghLqEio7GRfENvbJdS1+5FuFrBmtgkrskyYn67f1cCTfUHsuShhe4eEPr+C0mwz1mTr+zXfCuWAcsAqKhIquOJTUNfmx/YOCe1uGWtmm1CaY8b9U/g7OD69Goz8LrJtYuR3MdUiqN20uKMcfI7nHLCOikQCvdkROlN8p1OKnDU9OcOkdrOY8ecuKXI2/fRME0qzzXh2tv5+P5SDm+MlB1pBRSIBfveZH5vO+mA1CHg+L9Qp2Ix0hhSNO6Bge7uEN1r98AQVrJ9rwdfuNKvdrEmjHIyPXnOgNVQk4uj1Fh9qzvqRlSyiwmHGU3S2OG47uyRsOuvDJY+CCocZL8yxqN2kcaMcTJ4ecqBVVCTioKbJh5omP+alilg/10I7N2Jgb7eEmiY/TvfLqHCYUeFgv5MYmoOKuWYsn07FYbK0mAOtoyIRQ6+e8qKmyYfCDBMqHBY8Mo2/Bch4++ByEJvOevHB5QAqHBZU3p2kdpNuEM7BI9OMWD83iXIQB1rIgV5QkYiBHR0Sqo558OBUIyocFi53pyTap1eDqGny4aMrAVQvtGI1AwublIPEYzEHekNFYhKanTKqjnnR4gyieqEVK7NoWinRdl0MoOqYB/kpBlTfm4QCFS42oxyoj4Uc6BX9Jieo+qQXd9UP4Au3ifjrE3bqGFSyMsuIvz5hxxemiLirfgA/PelL6M+nHLBB7RzoGY0kxqm+S0LVUS/uShNRvdBK959hSItTRtUxD870y6hemIRVM+M39UA5YFcic8ADKhJj1OtV8N2/evDJ1SCqFybhmVkUPFa9dUFC1TEvFk0x4Gf3WZGRFLtrESgH2hHPHPCETn/GYFeXhEW7nchJEXH6KTt1DIx7ZpYJp5+yIydFxKLdTuzqkmLyfSkH2hKvHPCGRhK38C8nvNh8zo+ti634Eg1bNedPnRLWHfGgfI4ZP7hn4tskKQfaFqsc8IiKRBTdXgXrDg8ioABbF1sxM5kGXVrVOShj3REPjAKwdUkypo9j2oFyoB+TyQHPKPGj2Hl9WmHhbQbsXGajjkHjZiaL2LnMhoW3GbBotxM7xzjtQDnQl4nmgHc0khjh5RNevHbOh62Lk/E0TSvoTn1XAC8eduPFORb86CbTDpQDfRtrDggViYiAAqxudCMgK3htiQ1ZVhqK6tVFj4IXD7thFAXsKLJh6I1YKQf8uFkOyOdo/Azgsk/B8n0uzEoWUb8shToGncuyCqhfloJZySKW73Phsi90nkQ54Eu0HJDhuC8SzU4Zy/e5sCzTiJpFVrWbQxKoZpEVxRlGLN/nQsMliXLAqaE5aHbKajeHOVxPNx3+exCrG93457ssWE+3HObWfbucOHotiJfuSsIv7qP5aV5tavLh52d82FFkw5Lb6eaMYdwWiT2XAnj2oAubFyfj6/S0Ky71+RWsfN+Nv1wJAAAMArDziylYcQfdf4lXv/3Mj/Ijg3hzKeUgjMsiUdsu4fkP3dix1IZV9JQwLrW5ZDy5343T/cFhHxcA/HKRlR5mw7H6LgmrD7rxxsM2rM2m/oG7IrG52Y/qk178oSgZhdPoTIFHR68FsXyfG3/3jT7/LAoCKudZ8L/vpaknXh3qDeArhwZRNT8J5QV8zzRwtXBd2y6h+qQXWxZbqUBw6ui1IB5/L3qBAABZUfDqaR9eOeVNYMsISwozjNjygBXVJ734fZtf7eaoipuRxJ5LAfzDfhf2LrejkB4nyaU+v4K59U70eMe+g+WNh6x4Lpemnnh16HIAy/e68MdiG57I4nPqiYuRxOG/B/HsQRf+UGSjAsGpPr+CJQ2ucRUIAPjmXzzY2x2IU6sI6wqnGbFjqQ2rG9346AqfOdB9kWh2yljd6Ma/PUC3V+BVn1/B6kODaB4I3vqTR1AArHzfhaYJfC3Rh1UzTNi8OBmrG91c5kDXReKyT8HqRjf+x1wLvpHL9+ITz8o+9mDvpYnfzE2Sgft3O+mKXI59/U4zvjsvCasb3dzlQLdFInwPnn+YacJLc2lOmVe/OONDXQwWHt0B4L7dTgT46h/IEOsdFjwz2xy6txdHOdDtwvWXD7oxK1mkWyxwbFurH89/NBjT77lkqgEfldhj+j2JtlR84sGFQRl/XGpTuykJocuRxMsnvAjIChUIjh29FkTZx7EtEADw16tBlB2J/fcl2lGzyIqArODlE3xskdZdkdjZJeG1cz68toSPKk9u1OdX8I8H3fDGYY1RkoFftfjpgTWce22JDa+d83GRA10ViW6vgnVHPPQcAM5t+JsH513xu5tnUAGebRxEt1eXM7VkDLKsArYuTsa6Ix7d50BXRWLd4UF8K8+MVTPoampe/fKsD79qif8Vsr6ggpXvueL+cwi7np5pwrfyzFh3WN/Tj7opEv9ywouAAry8gO63w6s2l4zvHfUk7Of97VoQ3/44cT+PsOflBUkIKKH+R690USR2dUnYfM6PrYtpoZpnTx9wxWUd4mZeO+fDLg7mpUl0WxdbsfmcX7c50HyR6L2+DrF1sRUzkzX/csgEVZ/04kRf4p8qFlSAf/pgEL06n5cm0c1MFrF1sRXrjnh0mQPNXyfx9Q8HkZMi4ic0zcStNpcMR70Tflm9KD81w4T6ZbSjjmc/PO5Fm0vGbx9OVrspMaXpU+/6TgmfXA1SgeDcqv0uVQsEADRcklCv0+kGMjY/WZCET64GdZcDTReJqmNeVC+kAsGzX5714WS/+g+vl2Tg20doEZt31QuTUHVUX4vYmi0SPz3pw11pIp6ZRXd25VWfX0nobqZb6fbI+O7f2GkPSbxnZplwV5qI6pP6KRSaXJNodsq4q34ATatSkW/XbJ0jk/TcXwbxm/NsPTVMEICzq1JRQLnkVotThqN+AGd0kgNNvoKqo6FpJioQ/NrfE2DysZKKAnyHrp3gWr5dDE07HdPHaEJzveyODgktriC+dzetRfDs2x8PQlJ/KWJUh3ol7OjQ1+IlGZ/v3Z2EFmdQFznQXJGoOuZB9UK6aI5nb1+QcJqBxepoPEHgpU/0fasGcmvVC62oOqb9UaWmisSrp7x4cKoRK7Po3kw8++e/sn/gDQRCeSX8WpllxINTjZrPgaaKRE2TDxUOesocz96+IMX1Dq+x4pIU/KvGOwcyeRUOC2qafGo3Y1I0UyRqmnx4ZJoR908xqN0UoqL/qaEtpu4gNN9BkMm5f4oBhRkmTedAQ0XCj/VzabGaZ29fkNDiZH8UESbJoet5CN9Cown2duKNlSaKxOvnfJiXJuKRaTSK4FmlhkYRYa6AgtdbqFDw7JFpBsxLFTWbA00UiZomPyocZrWbQVT09gUJzRoaRYQNBhQaTRCsn2tBzVltjiaYLxK/+8yPO6wClk+n22/wTMu3OXBKCn73mTY7CBIbj003IitZ1GQOmC8Sm876sH4u7WjiWZtLxqdXE/w0oRi65lfwf07TaIJ3FQ4zNp3VXg6YLhJvdkiwGgQ8NYNGETyr1MFdNXs8Mt7UwdW3ZOKemmGC1SBoLgdMF4m6dj+ez6O1CJ71+RXs7NTeEH0kVzCUZ8K35/PMmssBs0Xiik/BO50S1mTTKIJn9V0BDGp3piliMKDg7QsSrvg0d9NlEkNrsk14p1NbOWC2SNS1+VGabYbNKKjdFKKin53W/lRTWKpJQB2Dd64liWMzCijNNmsqB8wWie0dNIrgXZtLxvE+HQwjrrvmV/AbDe5uIbG1JtuE7Rpal2CySJzsC6LdLeNJWrDm2tYW/XWoLU4ZJ3VU+Mj4PTnDhHa3dnLAZJHY3i5hzWwqELzT0pB8rDwBBdvbtXMWSeJjzWyTZnLAZJGoa/ejNId2NfHs6LXQaFJvfDLwnzosfmR8SnO0s8uJuSKxrzuAdLNAd3vl3K90ONUUFpQV7OsOqN0MoqL7pxiQbhY0kQPmigRNNREA2HWR/YNnoq74oJmpBhI/WplyYq5IvN8jYUUWFQme9fkVtLm0sag3EZ6ggv/XzX7nQOJrRZYJ7/ewnwOmikSLU4Y3CMxPp6kmntVp4Oxqsvr8iqaejUFib366Ad4g+zlgqkjs75FQnEEFgne/O6+9m6CNl90kYL8GziJJfC3LNDGfA6aKxIHeIJbRLcG5d7yf7TOrWPi7T8GBXv1OqZGxKc4wMp8DtopETwDFGUa1m0FUdPRaEC5JO/e1majBgIJ9l9g+gyTxV5xpxIEetjdpMFMkTvXLMIvAHDszTSIq0POuppEkJZR7wq85dhFmke0cMNMj7++RaKqJoL6Tn7Nrm5HWJQiwbDrb6xLMFInQVBMtWvPuhEbuZxMLl70y81MNJP6KMwxM54CdItEbpPUIzvX5FbgD+l+PCBsMAvsZX7Qk8cf64jUTRaLNJcMiArNtTDSHqOTDy+yeTcWLgFD+Cb9m20RYRHZzwESv3OyUUZDKRFOIiuq7+CsS6WYBzYxfTEXiryBVZDYHTPTMzU4ZjlRaj+Dd366xO+SOF0lWmO0cSOIU2KlI3FRTfwAFtPWVe5e9bB4k8eSXQ/knfHOkGZjNARM9M003EQC45OFn0Tqszy8zewZJEodGErdA002kz6/AE+SvSLgDwNkBNjsHkjhUJG5iMAh0e2TkpqjeFKIinjvKbo+MQf6WY8gQeXYDszlQvWdu6g/SKILgxDU252MTITNJQFM/g70DSagCu8hkDlQvEs1OGQV2KhK8++AKvyOJ25MMzE41kMRxpBmZzAEDRSJIi9YEHW72zqASxSyEjgPCt9C6BHs5UL13HvArSKf7+nHvso+/Reswd0DGgJ/f109C0k1gMgeqFwlnQIHdJKjdDKIyHp4hEY0kh44Dwje7SWAyB+oXCYmKBAk9hIdXPjl0HBC+2U0CkzlQvUi4AqAiQcDxbBM8QQUufjd3kevsJoHJHKheJJySghTa3BQTK1asQH5+vtrNmBAfhxfShQ0GaCRxKytWrIAg6PtkMsXAZg6YKBI0kiAye8dGwggCm50DSSyabooitHCtejOIygLsbQ9PGKekMLlgyZI9e/ZAUYb/jgRBQHl5+Q2fm5+fjxUrViSqaTFjN4lM5kD13pnWJAiA0NN3OMbiXDRJLFqTiEJr000rVqzAihUrcOjQIQiCEPlz6NChyL+HP7Zx48Ybvn7jxo3Dvm7k55SXlyM/Px/nz58f9nl1dXWRfw9/bLSzKABRv3Yi7Rj6OsPfJz8/P/KxWJ2xKeydQCVUoqYZhuYn2v/70H8fucZVV1cHQRBw/vz5YTkIZzH876N9bThzAEZtw9BMjsxVuF0jv8+WLVsiX1NTUwNBENDa2oqGhoYbjs3RXt9ox8bIY3u0jMfjGKDppii0uHDd0NCA5557DoqiQFEUlJSUoKioCIIgRD5eW1uLysrKYSEsLy9HZWUlWltboSgKWltbUVlZecOB2trairy8vMj3Lysrw9q1ayEIAnJycqAoChobG7Fly5Zbfm1tbS3Wrl074XYUFRVFvldpaWlkcTz8sUcffXTUA2282Ds0EisRnUN5eTm2bNkS+X9XFAWvv/46zp8/DyB0grNly5bIv4Wnd0bbDJGXl4dt27ZFMhburMMfC+dqtA403JErioJXX30VlZWVEAQBbW1tka9raGiIehK0YcOGSNvKysoiP6+iogKKoiAvLw8lJSWRjxcWFo76+hobG284Nurq6lBUVITa2trI57W0tAx7HfE6Bmwim2tTqhcJu0mAW4M7W1paWiJvP/fccwCA2tpalJaWAkDk74MHDwIInd1v2bIFtbW1yM3NBQDk5uZGDpKRWltbI29/9atfBQC8+uqr2LBhAwCgsLAQeXl5eO+99276taWlpZEDeiLtqK2tveF1P/roo5H3N2zYEHmtk6GdsWR8xHs0Pdr/OxD6/8zNzUVdXR0aGhrQ2Ng47Ou2bduG1tbWGzrB2traSOcb/v8vKSnBnj17Ip9TVlaGhoaGG9rS2NgYacOzzz4b+dzNmzcDCOWxpKQE77777mRfdsShQ4dueH2FhYUoKytDVVVV5GNVVVUoKysblult27ahoaEhMiKJ1zHgltmcemeiSDgltVsxPnl5ecPenzlz5rC/h35e+CztyJEjAIDFixcP+5zZs2cDQOTzwoYeyFlZWcM+Nyw/P39YsQr/zKFfCwCPP/545GAdbztGhv/xxx8fddQxWTrf3XhL8e4cwv/vIzMaFj6ZCXf8YeH3w/8eNlrWR+YuJycHwI2ZCucZ+Dzn4c8d+vGhJzuT9eGHHwK48fXl5OREfs758+fR2tqKpUuXjtrezs5OAPE7Blidejeq3YAUY+iXc4eVvV9OLHV0dAC4scBorR3hs73KykpUVlbecPY4YdobTMZUSoKOxKEd9EjRMqF2ZmOhra0NAG56rcXFixcBAGvXrsXatWujfl68jgGnpCQsB+Oh/kjCyOb9SuJl6Hzw0D8jz8JibeSBPpl2bN68OTIXfbO54/Ewqp5E9dhNAuxG9U+Sop25x/KMXm2jZX7k1tqh6xFD/wwdVcfjGHBKMhM5GEn1Q5PVFf1Ye/jhhwF8frYSL62trTcM7999993I4mMs21FaWoqSkpIbft5EmEX2Do5EsRmFuE8zhKcXw9NOI4WnWIbuBBr6/sgpGBZEG+GMttAebv/NshqeigqPtscilscAq9NNVCQSpLCwMLILaqi6urqYX/gz9ODZuHEjWltbI4tzk2lHeNvj0PcbGhpiMgpKMrB3cCSK1RD/NYnwYvDIaZTwduvwBofwJoywoqIilJSUxGRhNtby8/NHXdzOzc29YcE8/Poef/zxYR/fuHHjsFFAeAPH0GIZ3lIefjtex4ArSAvXo0ox8nOb5D179qCsrGzYHuxt27bFZk7/upKSEjQ2Nka+f2VlJRobG4ct2E20Hbm5uaiuro58TV5e3rBdKZORZOAjA6NJEhOzJrFnzx6UlJQM+39/4YUXIh1ceBPE0H8vKyuLaT5jafPmzWhtbY20dejHgc9fR/gsv6WlZdj1DeFtt0Pzu2HDBtTW1ka2tIdzHp6SiucxwOqahKCMnJBLsHVHBvGF2wx4cY5FzWYQlS34sxMn+th7KlciPDTVgAW3GbB1cbLaTSEqeu2cD3+7FmQuB6qPJOxGNi9FJ4llZ/AMKlGMIhsL10RdTklhMgeqF4lUs4BrDD6yjyRWplX1KKpHUZBqZq9zIInVJ4HJHKh+ZBbYDWh2cnwLUAIAyLNr7N4sMRRUBBRw/PpJSPOAzGQOGCgSIpr6ab6Jd8sy2Ds4EuWiR0aBXfVDkais2RlkMgeqt8iRRiMJAtx3O7+LEpe8Mhxp/BZJEtI0EGQyB6oXiWQDMN0qotXJ584WEnJHkgAer6dLNQm4wyoimb2+gSTQeZeM6YzmQPUiAYSmnGg0QdIZXLSLt6kWgckpBpJYTQNsTjUBVCQIQ2bp/CaPo6EiQYDwojWbOWCiVY40I5r6abqJd/mp/K1LKAjln/Ct2SkzmwMmigSNJAgAzE9jIo4J5QuC2TNIkjg03XQLBXYRzQNUJHj3zCyT2k1IuMtedqcZSOLQdNMt5KSI8MlAh5sKBc8W3GYATzeDnZcmIggBOSlMHIZEJR1uGT4ZzOaAmVYVZxhwoJcuquNdLqMHSjxYDQKKOb6IkIQc6A0wnQNmjsjiTCMO9NLiNe8enMbm4l08yEoo94RvB3qDTOeAmSKxLNOE/d2S2s0gKisv4OeW8T1eGcsy+VuHIcPt75aYzgEzReLuNBF+GThHu5y49uDtBhg5uPQ61STAIAi4m8MdXeRz55wy/DKYzgFTLSvONOJAD61L8C7bxlQs4yI3RWR6ioEkxoHeAPM5YOpopMVrAgBLGV7EixWDAKYXK0li7O+WmM8BU0ViWaYJ+3toXYJ33+FgXaJrkNYjSGjRmvUcMFUk8u0ikgwCTnL6rGMSct8UAxh8imPMzE01IMUkIJ/Ri6dIYpzsCyLJAOZzwFzrvphpwp6LNJrg3cLb2J6nnYxZyaGcE77tuShpIgfMFYk12SZs76Aiwbsf3KPfKaeOQQVrstnvHEh8be+QNJED5orEY9ON6PMr+PQqTTnx7EszTbqcciqwiwgqoZwTfn16NYg+v6KJHDBXJACgNNuMuja/2s0gKntYh1dfT7UIKM02q90MorK6Nr9mcsBkkaApJwIA//qFJLWbEHMtLppqItqZagIYLRLz0w3Iton4cxcVCp49NNUIm44GE7kpIgpSRcxPZ3tfPImvP3dJyLZpJwdMFgkAWDPbhO3tVCR49+VZ2hiSj4XdJGDNbG2cPZL42d4uaSoHzBaJ0hwz6tr9cAcUtZtCVPTje/Qx5ZRmEnC6P4jSHP0UPTJ+7oCCuna/pnLAbJGYahHw9EwaTfAuzy4yf7HRWMxPN+DpmSZMtehwyxYZs+3tkuZywPTRV5ptxhuttMuJd1XztT+aGJAUzexmIfHzRqt2djWFMV0knp1tgieoYCctYHPtG7lmpJu1c+Y10j3pBpjFUJ4Jv3Z2SfAEFc3lgOkiAQDr51pQ00SjCd5p+WFEohDKMeHbprM+TeaA+SLxtTvNuDgoY1833UKcZz9dmAQT82m9UWaSgIAcyjHh195uCZc8iiZzoInDrmKuGZvO+tRuBlHZP2nwAJtqEVDh0F67SWzVNPk1mwNNFIkX8i04PSDjg8t0PyeevfFgsqbu5zQ9ScBgUMALc7Q3xUBi54PLQZzulzWbA00UCQCocJhR00SjCd6t0dD+8ulWES/N1U57SXxsOuvV7CgCAARFUTRztdqMt/rxzrIU3D9FG5ezk/gw/r4PQcZTm5UsQpYVXPrHNLWbQlT06dUgnt7vQtcz2s2BZkYSAFDhsNBogmhibeLOFBEvaXAnC4mtmiYfKhzazoGmikTl3Un46EoAuy7STiee/eahZFgYTu496QZc9sqovFv7FwGSidt1MYCPrgQ0nwOGD7XRVS+0ouqYR+1mEJX9YlGy2k2IyiuHckr4VnXMo4scaK5IrJ5tQr7dgFdOedVuClFR+RwzclLYi++8NAPuTTdgtcauqiWx9copL/JT9JEDTS1chzU7ZdxVP4CmVam6uPkbmZhPrgbxwG6n2s2ISDUJcAUUnFmVigLKJbdanDIc9QO6yYEmX0GBXcTLC5Jo2olzi6YY8AxDZ2qzbSJ+ssCqi46BTFzVMY+ucqDZV1E1Pwln+mW8dYFu/sez/yqywWpQ/wq7HJsIRQG+P1/bO1nI5Lx1QcKZfllXOdBskQCA6nuTUHWM1iZ4938fUHdxMN0sQIGAV+7V9i4WMnlVx7yoXqivHGi6SKyaYcKiKQb88DgVCp59K8+Mhbepd4GlI9WApRkGrJrJztQXSbwfHvdi0RT95UDTRQIAfnafFdta/fhTJ0078ezdR1NgUmHW6c4UEZ1uGT+7T/tbHcnE/alTwrZWvy5zoPkikZEkYOtiK9Yd8aBzUFa7OUQlGUkCXluS2AM01SSgXwJeX2JFRpL66yJEHZ2DMtYd8WDrYn3mQPNFAgBWzjChfI4Z647QbieePZ9nwdrsxA31ZyaLeMlhxsoZ+ppeIOOz7ogH5XP0mwNdFAkA+ME9STAKwI9ofYJrvy+04Q5r/GPtSBUxxy7iB/foa5GSjM+PjnthFKDrHOimSADA1iXJ+HWrH+/Q+gTX9v+3lLg+xS4jScCAFMob4Vd9VwC/bvXrPge6KhLTI+sTg7jo0dyF5CRGCuwiti6Oz4GbahIQVELrENN1OP9MxuaiR8GLh93Yulj/OdBVkQCAp2aY8OIcC1487Fa7KURF38wzozQn9nPEs5IF/HeHBU/pdP6ZjM2Lh914cQ4fOdDkvZvG4ssH3ZiVLKJmkf62pJGxy357AB3u2Ox6u/c2A3JSRPxxqS0m349oU8UnHlwYlLnJge5GEmE7imw43hfEj0/QQjbPWr+UiqkxePjEwtsMsJkE7Cjio2Mgo/vRcS+O9wW5yoFui4RRCBWKP16QsImeZsctowCcXmWH1TjxeeMZySICCvDHpTZM4tsQjdvU5MPbnRJ2FPGVA90WCQCYZgmd+f38jA+/Pe9XuzlEJdMsAg5OcMfTVIsAWVHw1lIbplk46hnIML/9zI+fn/FhRxF/OdB1kQBCO112FNlQ/vEg6vEfGpAAAAcFSURBVGlrLLcWTTFg96N2COM4vm1GAe5AqEDo5bbPZPzquySUHxnEHwqTucwBF694ye0GvLk0Basb3TjUS8/H5tVjmQa88dDYtsamGAX4gwr+a6kND041xrllhFWHegNYfdCNHUX85oCLIgEAK+4w4o2HbfjKoUG800UjCl59404zXvmCFeJNhhQ2owCTCGx7OBlPZOl/iyMZ3TudEr5yaBD/8RDfOeCqNK7NNuGaT8G6wx5MKRJROE2920sT9fyveRYM+BX87IwPkjx8B/gcuwHX/DJ+vCAJX80xq9RCorZDvQGsO+LB9+dbuM+Bbq+TuJnft/nxzb8M4g9FNjyts3u/k7H791Y/Xjw8iHCdmJ8m4pxTxn88lMx9x8Cz+k4JqxvdlIPruCwSALD7YigI//ZAMr6RS0Hg1e6LEr50wA1HqojzThlvLrVxPbXAu9+e96P840HsKKIchHGzJjHSE1km7H0sBT847sUvz9J1FLx6IsuE7zgsONMv44U5FuoYOLapyYfvH/Ng72MplIMhuC0SAPDgVCP2PpaCN8776RbjnPrxCS/2XpKwc5kN7/UEKAec+vEJL37d4sPex1K43cUUDbfTTUNd9ilY3ejGgnQD3euJIxWfeCK3WJhmESgHnBqZAzIcFYnrAgqwutGNgKzgtSU2ZFkpLHoVvs2zURRuuMUC5YAfN8sB+RzX001DGYXQvXkW3W7Eot0DqO+ii+706J1OCYt2D2DR7cZR78VEOeDDrXJAPkcjiVHs7JKw7ogH38oz4+UF+n0sIW9+dNwbepLYYuuYngNAOdCn8eaAd1Qkouj2Klh3eBABBdi62IqZyTTo0qrOQRnrjnhgFEKPHB3Pk8QoB/oxmRzwjBIfxfQkAW8X27DkdgMe2OPCn+jmgJr0p04JD+xxYcntBrxdbBt3x0A50IfJ5oBnNJIYg13Xpx2eyzPjJzTtoBk/PO7FtuvTCitjMK1AOdCmWOeANzSSGIOVM0z45Ak72lwy5u104q0LdDbJsrcuSJi304k2l4xPnrDHrGOgHGhLvHLAGxpJjFN9p4SqY17clSaieqEV+RzeX55VLU4ZVcc8ONMvo/reJKyKY6dAOWBXInPAA0r2OK2aacKxJ+24J90IR/0AXjlFV+iy4JVTXjjqBzA/3YBjT9rj3jFQDtiU6BzwgEYSk9DslFF11IsWVxDVC61YmUWX8yfarosBVB3zIN9uQPXCJFWeHEY5UB8LOdArKhIxsKNDQtUxDx6cakSFw4L7p9BzKuLt06tB1DT58NGVAKoXWrF6tvpnjJSDxGMxB3pDRSKGXj3lRU2TD49MM2L93CQ8Qg81irkPLoc6hUO9EiocFlTezd4uo6E5qHBYUJhBI4tY00IO9IKKRBzUNPlQ0+THvDQRFQ4zlk+ns5vJ2tcdwKazPpwekFHhMKPCYVG7Sbc0PAcWLJ9OxWKytJgDraMiEUevnwt1EndYBayfa6FbAEzAzi4JNU1+XByUUTHXjBfytdcpUA4mTw850CoqEgnwu8/82HTWB6tBwPN5ZqzJNsFGdxSLyh1QsL1dwhutfniCCtbPteBrd2r/6YGUg/HRaw60hopEAr3ZIaGu3Y93OiWUZoc6iSfprDLiz10StreHfkdPzzShNNuMZ3W4EEk5uDlecqAVVCRUcMWnoK7Nj+0dEtrdMtbMNqE0x8zlbphPrwYjv4tsmxj5XUzl4OEvlIPP8ZwD1lGRUNnJvmDkrCndLGDNbBNWZJkwP12/HcXJviD2XJSwvUNCn1+JnE3r+TXfCuWAcsAqKhIM2dcdwPZ2Ce/3SPAGgeIMA5ZNN6E4w4g5Gr446JxTxoGeAA70BrC/R0KSQcAXM01Yk23CY7Tj5waUA8ISKhKManHK2N8j4UBvEAd6AjCLuN5RGFCcYcRsG7udRYdbxoHeAA70BrG/W4JfBoozjaHOLtNE9zkaB8oBURsVCY041X+9s+gJHXQWEShIFeFINaDALkbezk1J3IHX6gyi2Smj2Smjqf/62wMyfHLo7Lc404hlmSbcnUadQaxQDkiiUZHQqDaXPOTADETe7vbI1zsMAwpSRaSbALtJiPxJMYTfFyMfs4mAWwacknL9jwynpMAVHPoxBX0Shv286VYx1DHZRTjSjJG3cxLYQfGOckDijYqEzgwG8fnZnDOIAb8CZyB0cLsCQw72wPD3I52HEbAbheHvmwTYjQJSzUKo07GLcKQZkEzri8yiHJBYoSJBCCEkKhoPEkIIiYqKBCGEkKioSBBCCImKigQhhJCoqEgQQgiJiooEIYSQqKhIEEIIiYqKBCGEkKioSBBCCImKigQhhJCoqEgQQgiJiooEIYSQqKhIEEIIiYqKBCGEkKioSBBCCImKigQhhJCoqEgQQgiJ6v8DumQkDrX9qnkAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/mysql-join-inner-join.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f83c4a04-efdb-408c-9314-72e96a8b63f0",
   "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>member_id</th>\n",
       "      <th>member</th>\n",
       "      <th>committee_id</th>\n",
       "      <th>committee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary</td>\n",
       "      <td>2</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Amelia</td>\n",
       "      <td>3</td>\n",
       "      <td>Amelia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id  member  committee_id committee\n",
       "0          1    John             1      John\n",
       "1          3    Mary             2      Mary\n",
       "2          5  Amelia             3    Amelia"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT \n",
    "    member_id, m.name AS member, \n",
    "    committee_id, c.name AS committee\n",
    "FROM members m\n",
    "INNER JOIN committees c On c.name = m.name\n",
    ";'''\n",
    "\n",
    "# same as\n",
    "string1 = '''\n",
    "SELECT\n",
    "    member_id, m.name AS member,\n",
    "    committee_id, c.name AS committee\n",
    "FROM members m\n",
    "INNER JOIN committees c USING (name)\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c349caee-61a5-40a0-b373-3de99169232a",
   "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>member_id</th>\n",
       "      <th>member</th>\n",
       "      <th>committee_id</th>\n",
       "      <th>committee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary</td>\n",
       "      <td>2</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Amelia</td>\n",
       "      <td>3</td>\n",
       "      <td>Amelia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id  member  committee_id committee\n",
       "0          1    John             1      John\n",
       "1          3    Mary             2      Mary\n",
       "2          5  Amelia             3    Amelia"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.read_sql_table('members', engine).add_prefix('m.')\n",
    " .merge(pd.read_sql_table('committees', engine).add_prefix('c.'),\n",
    "        left_on='m.name', right_on='c.name', how='inner')\n",
    " .rename(columns = {\n",
    "     'm.member_id': 'member_id',\n",
    "     'm.name': 'member',\n",
    "     'c.committee_id': 'committee_id',\n",
    "     'c.name': 'committee'})\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0904d44-6f44-481c-adf3-6fcd33c09bf0",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL LEFT JOIN clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa410ae2-9d71-4a01-b003-690a5c316906",
   "metadata": {},
   "source": [
    "syntax"
   ]
  },
  {
   "cell_type": "raw",
   "id": "b4d554a4-12dd-4fcd-babd-afc2bcf74e4f",
   "metadata": {},
   "source": [
    "SELECT column_list \n",
    "FROM table_1 \n",
    "LEFT JOIN table_2 USING (column_name);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8f5f0c74-da4e-4863-8399-0e806890a887",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYsAAADiCAYAAAC/bOutAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH4wgcBhoY0Xd+VQAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAgAElEQVR4nO3de3xU9Z3/8df3nDMzuYebiRcgMQFCawtaUawloLQV+rO19VcLoRd3t/1tK7aV7V7ktyvupdXuyl66YAXto9rLbit4adVtfxJbLRi8IbaAbSWBYMJFTLglmSRzObffH8MMBAIJEGbmnPN5Ph48IJNM8p3hk+/7fC/nHOW6rosQQghxGlquGyCEECL/SVgIIYQYkoSFEEKIIUlYCCGEGJKEhRBCiCFJWAghhBiShIUQQoghSVgIIYQYkpHrBoizE7Nd3uxy2NBh8cZhm30xh+6kS68F/ZZLzHYxHRfLBcdVOK5L+uxLBSgF+tE/IU1RoEOhrigxFGMiiksKNa4cozO7QmfaaJ1CXeXy5YpTiNkuLT0OzT02LVGXHtMhakLUdIlabupv06XXJvPvqOlSGlKZPyU6xz420o9DWUhjSqmirkxnSpkmNRBwSs7gzm97+h1eOmDzygGLN7tsWqI2B+IupqsAF12BoRTW0WAYSZoC5+j3DGmKMWG4tETj6rEG14zTmVVhMKFIBqfZsKffOS4Ujv7d47Iv5hzr0Es1RoVP7PQHflxiQImu6LVTBxYnhsrxH3cl3WM/K+pySaHGlLJjPysdIlIDwSBhkWe2d9usbTf5xT6TN7tsTDc1EtAUuKRCIR9oClwXDA2mlOp8ZmKIhVUhppbruW6aL2zvttnQabO+w2RDp42mONZBlyrqynXqynRqS7LXUbf2pkPqWIA099g4Lsyp0LmuMsScCl1qwKckLHKsvc/hid1Jnt6bmk6KWy5KgVL5EwzDpYCQBpeV63y+JsynJ4SoKpajzuFo73NY32GxodNifYeF68J1lQZzKg2uqzSozuP3sS3d9o5U25U62vaKVNulBvxBwiIHmjotvrM9yW86TKJm6u2P6Ir+kZ5HyiGlUkOPIkNxw0UhvjE1Qn2FHHEer6nTYk27yW/eNTliupkj8+sqQ0wt824Hu73HyYyI1neYjA4prr8wRENVWGrAwyQssmRH1GZVi8l/v52gO+miawoXSNjBePs1oCSk+GJtmNunhJlcGsxOY0fUZm27xZq2JIYGC6tCzL8oxBVj/Pt+/O6wzbr9JmvbTSwHGqrDLKwyAlsDXiVhcR71Wi4/2pXk/pYkrT02KIXCxXRy3bLc0hRcWKjxd5dF+JOaMCWGv3fZ9Foua9qSrN1tse2IzcKqEA1VIa69IHibEV8+YLF2t8maNpP3j9JoqArTUB3yfQ34gYTFefDLfSb//laC9R0WKCjSFX0+mmIaSUrBNeMM7roswo2XhHLdnBH1y30ma9pN1rQluXlimIUTQ3x6or9e47l4cncqQH++O0lDdZiGqpDvasBPJCxG0JO7Te7eFqetN7VDRNMUMQmJYTE0uLBA8Z9XFnm+Q31yt8mK5gT9lsuf1UZYWBViXESOnE/lYMJhbbvFD1oTFBmKJXURz9eAH0lYjICftpks2xrn3VgqJBIBn2Y6FxowrkDjO1cW8tlqb3UYP20zWbE9gaG5LKkrYEGVt9qfDx5rN1nRHMdyFEumRjxXA34mYXEOHmlN8vfb4hxOuLi4xO1ct8g/NKUYFYZ/vaKQL9aGc92c03qkNcmK7QnGRlId3CfHSwd3rp7emwreQwmXJVMjeV8DQSBhcRZW70jwT9vidJsumvLXltd8oykoMRT/ckUBiydHct2cAVbvSLBie4KaEp076sLMv1hCYqQ17rdYsT3Brl6bJVMjeVcDQSJhcQYa91t8/fV+9sdSI4k+K9ctCg5dwbiI4kfXFjPvotzuImrcb3HXlhgTijWW1EW4rjJ4u5qybX2HxYrmBHv6HO69vDDnNRBEEhbD0BF3+cs3+nl6r4XtynRTLhkKbrwkxEMzi6gsyO6icUfc5a4tMX7TYXHv5YU0yJpE1q1pN7lrS4y5F4a4Z3pB1msgyLx7mmiWfLc5QfVT3UdPKJKgyDXLTc1nj/95Nw+0JLP2c7/bnKDumR7GFWg0f6JUgiJHGqpCNH+ilLERRd0zPVmtgaCTkcUpbOy0+PrmGK1RBwfkPIk8pCuoKtb58bVFfOiC83M28MZOi2Vb4xToinumR5gxVqY/8sXmQxbLtiaI2y73Xl543mpApEhYnMB04K/eiPHwriSW45KUbbB5z1CweEqEf/9AIaERGiubDvzNb2OsaU9yz/QC/s8kWVjNV9/fmWDZ1gQNVSH+dQRrQAwkYXGc1w7ZfOHlftp7bVwI/GU5vOaiQsXPZhdzzbhzO/p/7ZDNV17rx3Gh6YYSykMyL57vuk2X+ud60RQ8eHXhOdeAOJlk8FGrWhJ8qDHKzh4bQ1MSFB60P+ZybWMvq1oSZ/09VrUkqH8uyh11YbbdWCpB4RHlIcW2G0tZMjXC7F+dWw2IwQV+ZGG78OXX+vn5HpOYLQvYfpDeMfVEfRGGNrzO3nbhK6/18/sum4dmFjF9tMx/e9XWI6mR4WWjdB66unDYNSBOL9Aji02HLN77ix6e2G3SbyNB4RPpHVMTnoqy6dDQ/6mbDlnMeDZKRFe8Or9UgsLjpo/WeXV+KQW64qp1vcOqATG0wI4sHtyR5Kuv9+O6EMg3ICAU8MBVhSyeMvgC9YM7knzt9X4emlnEl+SSEr7z8M4EX9kU4/4Zp64BMTyBDIv0tFPSgR4zcC8/cAwFn7s0zA8/WDTg8S+/1s/WIw4PzSzkchlN+NaWo9NS00frfG9m0dBPEIMKXFgsaOqj6YBN1HTl3IkAMTTF9ZU6z80tAVJ1oClYM6s4xy0T2dKwsQ/Hhcfq5f/8bAQmLLpNl5tf7OePXTZdphuY25mKYzQF7xulMzaiUVeqsfrqwlw3SWTZ4k0xWnsdHq8vkp1uZygQYdHe53DT+j729DvEbIhLUARa2dFtllXFgd7fEVh/uyXO8+9aPF5fJDVwBnz/Tm3tcqh/Lkprr0PckaAQqXWqK/5flK1HZJdMEP3z5QV8emKIjzzfKzVwBnw9sljfYfGpDX3EHWTaSZykQIdnry+RS4wH1EM7EizbGufx+mKpgWHwbVg8tcfklqbUgpYvX6AYEQr42exiPjVBriIbRGvbk3z+pX4er5caGIovw2J9h8XcX/cCEhRiaAp44SMywgiq5/ZbfOyFXp6XGjgt361ZbOuyuXlDLxFdSVCIYXGBj6/vY5vMXwfSDRcZrKkvZsHGfqmB0/BVWOztd/jfL/Zjo5DLwYgz0We5fPSFPvb2yxUkg+gzE0N8c1qEBRv7pQZOwTdh0W+53NLUz5GEg+m49MsJd+IMdcYd5v66V2onoG6bHOHWS0Ms2NgvNTAI34TFgo397O2z6bPkyrHi7O2IOnz4+b5cN0PkyN+9r4Brx+ks2Nif66bkHV+Exa0vpy4tfSDhkpARpDhHmw5afHx9b66bIXLk3z5QSEWB4taXJTCO5/mw+Is3YjR1WnQl5RaoYmQ4QOM7Fos3xXLdFJEjj1xTRI/p8hdvSA2keTosHt6Z4Od7TI4kXbrl6rFiBFku/KA1wcM75Y5rQfV4fTEvdlpSA0d59jyLbV02Vz7bi+O6OJ58BcILNAW/+1+lTBsllzAPotcOWtT/qo/NHysJfA14dmRx22sxxoYVEc++AuEFjpu6nLkIppnjDL5zZQG3vSbTUZ7sau/YHONAwuFIMnUVWSHOp529Lp99SRY7g+qrUyLUlWncsTnYgeG5sPjxriRP7jHZH5MFbZEdtuPyWHuSH+9K5ropIkdWX13IbzqsQNeAp9Ys/thtc+WzUSwntQApRDYZWuo+GO8p89wxlhgBGw9YzHuhj80fC2YNeOoVL94UY1xEIyTX8hA5YDkuDbJ+EVizLjC4d3oBt70WzClJz4TFXVtiHEik7psdk3tTiBz5Q7fDXVuCPXcdZH8xNcKEYi2QNeCJaaitR2w+2NiL5bqYsk4hckxX8MbHSpk+OthbKYNqX79D3f9EeemGkkDVgCdGFndvi3NxoYahZPpJ5J7tEvidMUF2SZHGt6YVcPe2eK6bklV5Hxb//XaS3x+xOJBwZPpJ5I2XD1j899vB3RkTdN94T4QDcSdQNZDX01BJByY/3cMR0yUql/MQeaY8pOi8pZxw3h9yifPh+XctvvRqPy03lQWiBvL6JS7bGqMsrEjIiELkoV7LlemoAPvwhQbzLg6xbGswaiBvRxabD1nMfb6PhC0n34n8ZSh4ZV4JM8bKvZuDqDOeWuz+1dxi39dA3o4s7t6WYExYocuatshjlgtL5DLWgVVRkF7s9v+VafMyLB5tM2nrc+hKunLtJ5H33jhk82ibmetmiBz5Wl2EPsv1fQ3kZVisbE7Qk3TkHhXCExIO/OM2GV0E2V9OjbCy2d+ji7wLi6f2mBxOOPRauW6JEMP3dp/DU3v8fWQpTu1TE0K4ruvrGsi7sFjZnCBmQ4+MKoSHmA78fcBO0hID3eHz0UVehcVz+y3a+hyOSFAID3qr2+a5/TIkDqrPVoc5lHR9WwN5FRYrmxM4LvRKWAgPslz4uwBeYE4cc0edf0cXeRMWTZ0WW45YHEzISRXCu7Z12TR1+vPIUgztS7Vhmnv8WQN5ExYrmxOUGIo+/73HIkBMB/7lj/48shTD49fRRV6ExeZDFhs6LPbFZPpJeN8L71psPiRHPUH19boILx/wXw3kRVj819sm1SU6liNhIbwvbrs80BKcq5GKk902OcJ/ve2vbbR5ERZr25P8sdsmLmdrC5940sf77cXQFlaFWdvurwOGnIfFU3tMKgo0QjlviRAjp9/y9wla4vSmlGm8t1z3VQ3kvIte257EcaErKVNQwj9sFx7a6a8jS3Fm/Da6yOklyrtNl9GPdSMxIfyq6zPllIfl0slB1JV0GfN4N0d8UgM5HVmsbTe5coxOseH9N1KIE+kKXx1ZijMzKqxYUBXyTQ3kOCySHEi49FkythD+Y7vwn9v9t99eDF9qKsof6xY5C4sdUYetR2wOJCQohH/t6nXYEZWrEgTVzRNC/L7b9kUN5Cws1rYnmVyqIzNQws8sV6aigs4vC905C4tn95l0xF25FLnwNduFH7V6v6MQZ+9TE0I8u8/7U1E5CYse02XzYYfdfXIWnvC/XX2OHBQF2JwKg82HvV8DOQmLFztt3luuURaSOSjhf4pUzYtg0hXMqTQ8XwM5CYsNnRYFuuKInIgnAsB24Zm9MhUVZHMqDDZ4/LLluQmLDot2mYISAdLo07unieGZU2mwocPbNZD1sDiUcPlDt8WBuIwqRHDs63c4JNvEA2vWBTp/7LY8XQNZD4sNnRZTS3W5cKAIHK9PQ4hz4/WpqOyHRYdFSIeYzEKJALFd+J+93t8+Kc6e16eicjKy2N3n/bMZhThTz7/r3Y5CnDsZWZyBLtNlV6/DEdkYIgKoI+7S5fG99uLszRxn8Hav49kayGpYtPQ4jC9SFOjZ/KlC5IeInvodEME1pUz3bA1kNSyae2xGhRSm3GtbBFDMdmnukcW6IJtSqnm2BrI8srDRlKLPu9N2Qpw1y4HXD3mzoxAjY0qZRouExdBaog5Hkt4cggkxEn7f5c2OQoyMKaUaLR69XHnW1ywOyyU+RIB5taMQI8PLaxZGNn9YS9SR9QoRaJ1xb3YUYmTIyGIY2nodRoWQmx2JQNMVtMl5RoFVGlKMiyhP1kDWwqIl6lBZoFGgS1qI4HJc2T4bdFNKNU/WQBbDwqYkpLC89x4JMWIcUr8LIrimlOmerIGshcX+mIuuIGrJmoUILsuBtz06Zy1Gxvgijf0x7/WDWQuLqOnKxQOFAN6JSVgEWYmR6g+9JqthkZSdUELQLwdNgVYaUhIWp9NrQcL23hskxEjr9mBHIUZOaUjR68GrWGR1ZJGQIyohOCR3iQy0El2moU4rajkkZKpWCPpkhB1opSFF1IPbQrM6spCzt4VADpoCTtYshtBryS+JEABx2T4eaLJmMYSo6ZKU4bcQyOWhgk22zg4htXU2Wz9NDObgLeW03FSW62YEXlB+DyZNmsSkSZNy3Yy8UxrSJCyEEEL4U9bCojSkCEs0CRGY34OdO3eyc+fOAY8ppbj99ttP+tpJkyYxf/78bDUtp6KmQ2nIexdUzWpYyBVnhYCw/BoEWq+FhMXplBgQDkBYHLylnFfmlXD/jELcz43K/KmvMDKfTz92/4zCk57/9JziAc97ek7xgM+33FRGy01l3FEXGfB1d9RFMp8/1XPTTvXcs2nH8a8z/X2Of42yRnKyojPsKG6//XaUUpk/y5cvH/D5+fPnD/j8iUfoa9asQSnFrl27mDRpUubr0kf46c8rpU5aY1i+fDlKpdo7WBvSnx/s586fPz/z/Y7/PqtXr848Z+XKlSilaG1tpbGxMfP4xo0bT/n616xZc9J7dPxrON3o5VRtzaao6VKS1dvOjYysjiyCcuOja8YZzLs4hPpJF+onXRxKuLz40RLcz43im2/GUT/p4pm9Jl+riwzoqF+ZV8JN40PM/lUv6iddzP5VLzeND53UUU8u1fj79xdkvv+OqMOKo5124zsm6iddfLc5wU3jQycF0onPfWavyYoZhWfdjkXV4cz3WtmcoOWmMg4n3cxjb3Xbg4ZikEXO4Lfu9ttvZ/Xq1bS2tuK6Lq7r8r3vfY9du3YBqQ5w586dmc+5rktjY+OgnWFtbS0//OEPcV2XRx99NNNppx9zXZfW1tZBn5vu0F3X5b777mPp0qUopWhra8s8r7GxcdBOGuDOO+/EdVOLuosXL878vDvuuAPXdamtrWXevHmZx2fNmgWkAmf16tWZx5uamli0aNGAwFi+fDmLFi2iqakp83WrV68e0JZ0cKU/P3fu3EFDJxuipisji9MpNbQz+iXxskMJlynP9GQ+fulAalP1ks0xVjYnAPi3t1J/f/jC1CFGfYXBNeMMvtucoKkz9fVNnRbP7DW5aXzopJ8x7onuzL8b3zEB+G5zgq9vjgFk/p4xVj/tcz+5oQ+ARdWhs2rHN9+MD/h4TFjxVvex67p8ckNfpi0ipXiYI+xdu3axevVqHn30UWpqajKP79y5k5qaGpYvX05rayvPPffcgOc9+uijNDY2Djg6Tz+e7oQbGhoAmDdvHuvWrct8zeLFi2lsbDypLU1NTZk23HLLLZmvXbVqFQA1NTXMmzfvpLaci40bN9LY2EhTU1PmsVmzZrF48WKWLVuWeWzp0qXcd999mdcGZMIwHao7d+5k7ty5mc/feeedmfcg26KmS6nhvc4wqyOLyMn9li8dTg7cFrf76C0Uf3fkWCea7ogrClIdx4KJqY74sd3moM9NT2NBKoyOt+Po/RF2nHCfhEMJl7EnJPSJX5N+bHKpflbtSIffse9lDzoKEceUnpy5g9q0aRMA48ePH/TzL7zwArW1tQOCBODqq68G4OWXXx7w+InfZ7DnVldXA2Q62bSLL7448+/0c9Jfe/zjra2tp3o5Zyzd/uNDIP1z0z8nHYjXXnvtgK9Jv9Z33nkHgBtuuIGlS5eeNIWXC722N9cssjZzVmJAUQDWLM7WxOJUp/7iR0s83Y4PNvZmprHcz41iR9QZMMoSUH6GK9zHd9QnGuw8hhMDwKva2toAMmsdg9m7dy8A9fX1p/1e6RHQ0qVLWbp06Ukjqmzy6ppF1pqcGllIWAxl9q96M6OObDpxNHQu7fhgYy8A988o5Gt1EV6ZV5J5TEDlCM7Hnrg1FU4eFXhdeq3jdJqamk4agZxo1apVrFq1ijVr1rBo0SJuv/32TIhkk6xZDKE0pAh5b5oua55/N9UxXzH6/M7VTS49+T9hcqnGoaMX7hrJdnx9c4wdUeekqbCgKx7mIVp6Oik9HXWiuXPn0traelI4pL/+xKmZfFBbWzvo44ONkGbPng2cPvzS71F6hDEcDQ0NzJs3L2ehKltnh3BRocJ2kdHFKaxsTmR2NR3v/hmFI7799OAt5Zl/vzIvNd105+/i59yO+gpjwPeurzAGBJFIec+o4QVxetF40aJFAx6fNGkSu3bt4s477wQYsOtn165dLFq0iMWLFw95pJ0LkyZNGnQRvKam5qSF9YaGBmpra7nhhhsGPL58+fLMa66pqWHx4sUsWrRoQOe/cePGTACltwyn7dq1i8bGxpxN1+3td7io0Hv9YNamoaaU6vSaScIachOkU5jyTA+vzEttsU0b6Tn/Vw9abD5kD/gZJ045nW07mjotHm1LDnjeqwctmYI6TqGe+l0YrnXr1mXOo0i77777Mh2d67qZcweO/3w6SPLNqlWrqK2tzbQ3PcW0atWqzFZegNbWVmpqati5c+dJr//4XVjp51ZXVw8YtdTW1mam6GpqarjnnntO+z2yqaXH5pOD7CzMd8odzoTgCGjrc/hQYy/dpkOfBy/PK8RIiOiK7R8vpbpEpuaCquqpHjZ8pMRzNZC1kUV1sUaX6ZKUUYUIMNtxPddJiJETNV0OJrxZA1lt8ZRSjXEF3nuThBgp4wq8N1ctRk5L1GbKIJtMvCC7YVGmUe7BXQBCjJTakoCcmSoG1dLjMKVMwmJIU0o1xkUkLERwfWCMhEWQtUQdGVkMx5QyHcd1h73PXAg/MTS4apBrdYngSI0svFkDWQ2LujKdLhO005y+L4RfhVXqd0AEV0vU9mwNZH3NYm+/Q8L23v1nhThXlqs8O18tRoasWQzTqJCipkRjrKxbiAC6uFAxSjZ4BNZrBy0uLdE8WwNZj7g5FUbmyqZCBMl1lbJYF2QbOi3mVHi3BrIfFpUGpp267IEQQRHW4BMevMSDGDkbOizmePiAIScji+1RG0uWLUSA2C6ePqoU505GFmdobERxWbkh51uIQKksULJWF2AbD9i8t9zwdA3kZPFgTqVBVbHMQ4nguPESmYIKMq9PQUGuwqLCIG67FMq9LUQAFOqKm8aHc90MkUNen4KCHIXF7AqdP3bL+RYiGBKOy+wKGUkHle2mRhZer4GchEVZSDFjjMZ42UIrAmBKqUaZR/fWi3O3odNixhjv10DOeuuPXRLi4kJNbrMqfE1X8OmJMgUVZE/tMfmYD9aschYWC6vC7IjaKGQqSviXruBPaiQsgmxte5KFVd6vgZyFxeRSjemjdCrlZkjCx+rKdCZ79JLU4tz9fI/J+0b5owZy+goWVoW4IKIoNmQqSviPruCLtd4/ohRnzy+jCgDlum7O5oG6ky6jH++WiSjhW10LyuXukAHVlXQZ83g3R3xSAzkdWZSHFQurQlxW7u0tZUIMZtYFui86CXF21rabLKwK+aYGcj6RtrAqjKagQPJC+EiBDn/1noJcN0PkkJ+moCAPwuJTE0J0xh0MuXue8BFdpWpbBFNLj8Mfu21f1UDOwwJSo4v3yFSU8JHPyLkVgea3UQXkSVh84dIQbb02RbIrSvhARFd8dYq/OgpxZh7ckeALl/pnVAF5EhYzxhrMqTSoLpawEN53zVidGWO9fdE4cfbub05w7QWG72ogL8IC4I66CN2mS7G/3l8RMBEdvjVdFraDbGVzgjvqIrluxojLm7CorzC4fLQhZ3QLT3tfuU69xy9FLc7ew61J6sr8WQN51TPfURfBBcKaTEcJ7zEUfPvywlw3Q+SQX0cVkGdhccNFBtXFGpUFEhbCeyaX6dxwkf+OKMXw/LQtydiw8m0N5FVYQGp0UahDkeykFR4S0eDbslYRaCu3+3dUAXkYFp+aEGJMRGNUWEYXwjvGF2m+OgFLnJmn9pgopXxdA3kXFpAaXZSFNUp8ck0V4W9FhuJb02WtIsj+w+ejCsjTsFhUHaK6WONCWbsQHvC+cp1F1f49ohSn993mBMWG8n0N5GVYAHxrWoSOuEsob1soRGoH1ANXyVpFUHXGHe7eFudb0/w9qoA8DosZYw1umxymrkxWukV+0hV87tKw787UFcN397YEfz4pGDWQ05sfDSXpwOSnezhsuvSaedtMEVDlIUXnLeWE8/aQS5xPz79r8aVX+2m5qSwQNZDXLzGswb2XF3Bhgdx6VeQXQ8HyDxQGopMQg1u2NcY90wsCUwN5/zI/f2nq8uUXymVARB65aqzOlyfJlWWD6jtvJbigQOPzlwanBjzRA39rWgHvxBwMuQyIyAOGgtVXF+W6GSJH9vWnF7WDtbHBE2ExfbTON6aGmVyqyVVpRU6FFCyZWsD00bLxIqiWbomzpC4cuBrwRFgA3Ht5IRdEFOWyl1bk0OQyjX/7QLCOKMUx/7k9wZ4+h3sDeMFIT/W8D84s4nDSQZfZKJEDhqZ4vL44180QObKx0+KurXFWXx28oACPhcV7yjQeurqIiwo1CoI1AhQ5FtLg/hkFvFfuFR9Icdtl8esxVl9VGNga8FRYANxaE+bmCSHGF3mu6cKjdKX4xCUhbpvs/7N0xeAWb4pxfaXBrTXB2f10Ik/2uCtnFHJBROOiQk82X3jMhCLFk7Nl+imoHmhJ0NzjsHJGMKef0jzb2z44s5ADCRfZTSvOJ03B09dJUATVawctvvFGnAdnBjsowMNhMW2UzoNXFTC+SKNEzu4W50FYU6yYUcS0UcGcow460+HoOkWB1AAeDguAL02KcPOEEOMiinK594UYQYaCz1WH+NqU4M5RB91nmvqYXWHwpUmyVgV5fiHB4br15X5e7LTYH3NIOrlujfA6DfjYJSF+IdNPgfXFV/uxHPjxtXKmfpqnRxZpP762iPeN0hkbkdGFODeagivG6BIUAfbXv43RGXclKE7gi7AAeGxWEROLdSrk7nriHFQXa7z40ZJcN0PkyLd/H+flgzaPzZKgOJFvwqLIUDxRX0RpSKNU1i/EWRgdVmz4aAlFsmEikB7ckeDHb5s8NqtIamAQvgkLgPFFGj+rL8JQUCDXBBFnIKLB+o+WyMmeAfVYe5K7t8Z5rL5IauAUfPeuTBut87PZxSRsFyV5IYZBAevmlsj2yIB6br/Foo39PF5fLDVwGr4LC4DrKg1+NrsYjVRHIMSpKOBns4u5rsPFDMAAAAi1SURBVFKufR9Ej+02ufE3vTwpNTAkX2ydPZX1HRaf2tBHzHZlS604SURXrLteOomgemhHgmVb4zxeLzUwHL4OC4CtR2w+sb6XQ0not3z9UsUZKA8pNtxQyvRRvhxciyHc94c4329N8sTsEqmBYfJ9WAC09zl8ckM/u/tsjiR9/3LFEEoM+P3Hy6gqlk4iiP52S5xf7zd5Ynax1MAZCERYAHSbLje/2M/vuywOxAPxksUJNAVTy3QqCzXqSrXA3sQmyBZvitHa6/B4fZFcIugMBSYs0hY09dF0wCZquvTJtFRgaApmVxj85iOpE+4WNPWhKVgzS87UDoqGjX04Ljwmdzs8K4Ebgz1WX8wnLjEo1JG77QWEoWDBxFAmKCBVB2Uhxcx1UbYcsXPYOnG+bTliM3NdL2UhJUFxDgIXFgDfm1nEN6cVkHRka63fKeC+Kwp5dJARxPdmFvGnNWFmPBvl4Z2J7DdOnHcPtyaZ8WyUP6sN872ZcgmPcxG4aajjbTpk84WX+9nXb9Nn5bo1YiQpYFxE8Yvri7l67Om3RW46ZPOV1/q59gKDB66SdQy/+OrrMV4+YPHQzMIha0AMLdBhAWA5Ll/eFOOpPSZ9FiSdQL8dvmAomFNp0Di3hOFe9cVyXL6yKcYfumwemlnE9NEyR+lVW4+kwv99o3Qemlk07BoQpxfIaajjGZrikWuKuGd6AbbryrSUxyngX64o5NcfHn5QQKoOHr6miFtrwly1LsoPdiXPWxvF+fNIa4Kr1kW5tSbM96+RoBhJgR9ZHO/Vgxa3vhKjvdfGdsGWd8ZTKgsUT19Xwsyx5zYqePWgxW2bYjguNN1QIlssPaDbdKl/rhdNwUMzi865BsTJAj+yON414wz+cGMpX5kUIaIrQvLueIKuFH9aE2HPzeUj0klcM87g9fmlzK00qHsmyvdl8TuvfX9ngrpnephbmfp/k6A4P2RkcQovHbD52uv97Ig6WK5LQnZX5h0FTCjS+MmHiphVcX4WMF86YHPXlhgFuuKe6RFmyEJp3th8yGLZ1gRx2+We6QXnrQZEioTFEB5oSfI3v42RdFyZlsojuoJ/vryQv3lvJCs/74GWJHdtiXHblAj3TItgaDI1lSuW47JsW4IHWxLcM72Ar9VlpwaCTsJiGDriLn/129SOKdt1icsoI2d0BfMuDvHINUVUZvkWuh1xl2Vb47zwrsm9lxfSUBXK6s8XsKbd5K4tMa6vNLj38sKs10CQSVicgcb9Fl9/vZ93YqlRRlyGGlmjq9R5Ez+6tph5F+V2uqFxv8VdW2JMKNZYUheRy1tnwfoOixXNCfb0Odx7eWHOayCIJCzOwuodCf5pW5wjSRelFAkJjfNGASUhxT++v4C/fE9+TTes3pFgxfYENSU6S6ZGpAM7D9a9Y7KyOcmuXpslUyMsnpxfNRAkEhbn4JHWJP+wNcaBJLiunNA3khRQHlZ8a1r+z0k/0ppkxfYEYyOKJVMjfHK8TE+dq6f3mqzYnuBQwmXJ1AhfrA3nukmBJ2ExAn7aZvL3W+Psjdk4LphyV76zpgFjIhrfvjzCn0/K75A40U/bUh2cobksqStggaxpnLHH2k1WNMexnFTwfrZa3sN8IWExgp7cbXL3tjhv99q4LiQkNIZNV1BZqPHP0wu4tcbbR5FP7jZZ0Zyg33L5s9oIC6sMxkXkpJ1TOZhwWdtu8oPWBEWGYkldhE9PlJDINxIW58Ev95n8+1sJ1ndYoFJTVGJwSsGMMQb/8P4IN17irw7il/tM1rSbrGlLcvPEMAsnGnx6oreDcCQ9udtk7W6Tn+9O0lAdpqEq5Lsa8BMJi/Oo13L50S6TB1oS7OhJTVHJYCO1HlFRoPjr9xZw2+QwJYa/tz/2Wi5r2kzWtCd5s8uhoTrEwokhrr0geAviLx+wWNNusrbdZNponYUTDRqq/V8DfiBhkSU7ojarWkx+2pbkcMJBU5AMSHKkz18r0mFBVZj/e1mEyaXBvCTDjqjN2naLNW1JDA0WVoWYf1GIK8b49/343WGbdftTAWE50FAdZmGVEdga8CoJixxo6rT5j7firO+06DFdFArbZ/8NSqXm3woNxTXjdP7x/QXUy+UYBmjqtFnTnuQ375ocMV2uqwwxp0LnusoQU8u8u8axvcdhfYfJhk6b9R0mo0OK6y8M0VAVkhrwMAmLHGvvc3hid5Kn91psPmyTsFOXSffaqRtKAS6ENLisXOfTE0N8/tIwVcXe7fSyqb3PYX2HxYZOi/UdFq4L11UazKk0uK7SoDqP38e2dNs7Um1X6mjbK1JtlxrwBwmLPLO922Ztu8kv9pm82e2QtF00pdCUmzdbcrXjFu11BXVlOp8Yb/Anl4aZWi5TCyNhe7edOTLf0GmjHX2fp5Sq1N9lGnVlOrUl2euIW3sdmntsmrttWqIuzT02LVEHxyUzIppToUsN+JSERZ7b0+/w0gGbVw5YvNll0xx1OBh3MycAGip1VH8+gkRTkD7P0FBQFlbUFGtcOdZgToXOrAqDCUVy1JgNe/odWnqcTAfd3GPT0uOyL+YcC5BSjVFhRWlIUWoc/Ts08OMSA0p0Ra/t0mtB1HSJWm7q7/Sfox93Jd1jPyvqckmhxpSyYz8rHVpSA8EgYeFRMdvlzS6HDR0Wbxy22RdzOJxwidnQYzqYDkevlKuwHRcXSP9HK5XakaQrha5AVy4FmqLQUBQbirKwYnyh4oPjDGZX6EwbrVMotxzLSzHbPS5EXHpMh6h5cgj02gwIhOODpETnpFApDUFZSBswkpEaCDYJCyGEEEOS8aMQQoghSVgIIYQYkoSFEEKIIUlYCCGEGJKEhRBCiCFJWAghhBiShIUQQoghSVgIIYQYkoSFEEKIIUlYCCGEGJKEhRBCiCFJWAghhBiShIUQQoghSVgIIYQYkoSFEEKIIUlYCCGEGJKEhRBCiCFJWAghhBiShIUQQoghSVgIIYQY0v8HgtW2OH/lA+0AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/mysql-join-left-join.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c2fb73f2-ac70-4956-b264-b2c7f8c34d90",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    member_id, m.name AS member,\n",
    "    committee_id, c.name AS committee\n",
    "FROM members m\n",
    "LEFT JOIN committees c USING(name)\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bbab0621-c092-4ba8-92c4-04781316d853",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=(pd.read_sql_table('members', engine).add_prefix('m.')\n",
    "     .merge(pd.read_sql_table('committees', engine).add_prefix('c.'),\n",
    "            left_on='m.name', right_on='c.name', how='left')\n",
    "     .rename(columns = {\n",
    "         'm.member_id': 'member_id',\n",
    "         'm.name': 'member',\n",
    "         'c.committee_id': 'committee_id',\n",
    "         'c.name': 'committee'})\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6dd25a12-0e3b-4268-8c69-b8115b13e605",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ca5e08be-245c-466f-958d-97a65c9e5291",
   "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>member_id</th>\n",
       "      <th>member</th>\n",
       "      <th>committee_id</th>\n",
       "      <th>committee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>1.0</td>\n",
       "      <td>John</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>David</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Amelia</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Amelia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id  member  committee_id committee\n",
       "0          1    John           1.0      John\n",
       "1          2    Jane           NaN      None\n",
       "2          3    Mary           2.0      Mary\n",
       "3          4   David           NaN      None\n",
       "4          5  Amelia           3.0    Amelia"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ddde4ab-5501-4112-a6d2-4eb83fb986ab",
   "metadata": {},
   "source": [
    "To find members who are not the committee members:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3add1a88-e597-4671-a5b6-50059e0fd51d",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    member_id, m.name AS member,\n",
    "    committee_id, c.name AS committee\n",
    "FROM members m\n",
    "LEFT JOIN committees c USING(name)\n",
    "WHERE committee_id IS NULL\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "556fdf81-dfb7-44c8-8634-fa9862c4f7ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=(pd.read_sql_table('members', engine).add_prefix('m.')\n",
    "     .merge(pd.read_sql_table('committees', engine).add_prefix('c.'),\n",
    "            left_on='m.name', right_on='c.name', how='left')\n",
    "     .rename(columns = {\n",
    "         'm.member_id': 'member_id',\n",
    "         'm.name': 'member',\n",
    "         'c.committee_id': 'committee_id',\n",
    "         'c.name': 'committee'})\n",
    "     .query('committee_id.isna()')\n",
    "     .reset_index(drop=True)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e7aa7079-ad59-421f-908d-795e114ef99b",
   "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>member_id</th>\n",
       "      <th>member</th>\n",
       "      <th>committee_id</th>\n",
       "      <th>committee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>David</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id member committee_id committee\n",
       "0          2   Jane         None      None\n",
       "1          4  David         None      None"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "2c86d248-5d45-44cd-a1be-e69167367115",
   "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>member_id</th>\n",
       "      <th>member</th>\n",
       "      <th>committee_id</th>\n",
       "      <th>committee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>David</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id member  committee_id committee\n",
       "0          2   Jane           NaN       NaN\n",
       "1          4  David           NaN       NaN"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "726c9689-0e6c-4be7-8f65-894a43f2a19e",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL RIGHT JOIN clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0609582b-e7b0-4022-915b-f2929351f374",
   "metadata": {},
   "source": [
    "syntax:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5eaa07ea-0612-4ff6-a856-1038b4ab0f24",
   "metadata": {},
   "source": [
    "SELECT column_list \n",
    "FROM table_1 \n",
    "RIGHT JOIN table_2 ON join_condition;"
   ]
  },
  {
   "cell_type": "raw",
   "id": "e2d60da2-fcd1-4297-bcc7-992c3f2406fe",
   "metadata": {},
   "source": [
    "SELECT column_list \n",
    "FROM table_1 \n",
    "RIGHT JOIN table_2 USING (column_name);"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c7a91b42-bad3-4e54-97eb-6d37e00b9c99",
   "metadata": {},
   "source": [
    "SELECT column_list \n",
    "FROM table_1 \n",
    "RIGHT JOIN table_2 USING (column_name)\n",
    "WHERE column_table_1 IS NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2fd5c7e5-f937-4f5d-88c5-f728d036fd13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAWUAAADcCAYAAACoN7CyAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH4wgcBho6BBc/sQAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAgAElEQVR4nO3de3hUZZ4n8O97zqlbKpVwS8I9SEKCCqIi8QIh9KLAeEF3Bwcdd32wnXHAbaHHdR97Rsb1sdHt7vFhB+gR1t7Zcd22hW63W5DWRGXaGBCkQQHlksTQBEhCQrikKnU9l3f/OKkyN26Vc6pO1fl9nicPpFKpOgfe8z1v/c573pdxzjkIIYRYgpDuDSCEEPIdCmVCCLEQCmVCCLEQCmVCCLEQCmVCCLEQCmVCCLEQKd0bQC6vOaihwa+iIaChLcwRkDkCSs+fMke3gj7fB2R9hKPPwb77khhyJfT53udgGONhKPMJKMsTUeyl87OVUTuwD0bjlNMvoAD1XSoaAvpBp/9dQ4Nfw3BXzwHjEzDeKyJXHHig+Rx9DzYAfQ/WfgdwQOboVoHTwZ73CWi4EOUoy9Pfpzxf7HlPEeX5Inx06k6Jy7WDPCdDkZshV2IQGSBrQEjhUAGEFCCicihc/zOmcsQ0/TWdAuAWGZwig8R6/i5weCUBTgFwCIDKgW6Foz3C4Y9RO0g3CuU06JI5Pm1XUNuuoLZDQUNASwSvfiD0HAx5AvJ6QtZsfpmjwa8NDISebasqlFBVJGFekYT8FG1TtuvfDur9GiZ4BQx3AAIDLsaA8zGOc1EOh6A/Bg7InCOiGrcdPonBLenhLmvASBfDCCfDKDdDTOW4IAOnghrK86gdpAKFcgpEVODTdhm1HQpq21V8dUFBVaGEeUUOVBWJuHOUtbsguzv17Y7vwy3DJVQViYl9cIvp3sLMMFg7uD5PhEtkaAlpaI9wuERAYsCFmDUOS6/E4BD0wC5yMxR7BQQVjqN+ldqBSSiUTdIc1LClWcaHrTJq25VE76KqUP9iGdrJ4Bx6qHQoiV5eVZGEPxvrwNJiB9Uk++nfDqYPE+F1MJwOqmiLAB6BQ+YwtOdrNo/IENM4RnsETPQKCMocX19UqR0YhELZQBGVY0uzjC3NMnafVfDIJCcemuBAVaEIt5ihKXwFEZWjtkPFe6dkbD4Rw50FEpYW6wdmtu7zlfRuB7s6FJTnizgf4zjercIrMigGlx/SbZiToUsGJucKKHAxHLmoYnYhtYNkUSgboLpVPwC3nJSxcIyEpRMdeGSSM92blRZbmvVwrjmjYOlE/aBcNNaR7s1Kid7t4Po8AX6Z41RQg1NgCCj2OcxcAjDBKyBPYjga0GzXDoaKQjlJR7pUvHU8hi0nZYx2MzxS7MTSYgdGe+hjGwCcCWuJgGoL6wfm45OduCE/uwqPvduBWwAYY2j0q/BKDF2yvQ8thwBoHJg2TERMA8Iqz9p2YCQK5Wu0/7yC9fUxvH9axvIpLiwtdmDGcGpgl3PwgootzTI2NUbxwHgHVpY7MXOEtS9uXkm8Hbx3SkZ5noCmbg1RFQjaqEd8LfIcDAIDrs8XcfiiiocmZEc7MAOF8lXadVY/CHeeVbCyzIlVU910tfkaRVRg3bEI1jfEMKdAwspyJ2YXZNZBGW8Hf2hXUOTWe8WMMURUOoyulksApuSJaI9wfK8oM9uBmSiUr2DHGQXrjkVxuEvFqqlurCy3Z63YaOvrY1h3LIIb80WsmurC/NHWPijj7WD/eQVeh4DWkAoNDGHqGSfNLQKTckX4YxpmjpAyoh2kAoXyJWxvkbG+PoaWkB7GT5VSGJvhjW/1cB6XI2JluRP3j7PWxaB4O2jwq5AEhtawRkFsMI+k363IOVCWZ812kEoUyv00BjS8cCCMhoCGZ6e68PhkCuNUeOt4DGuPRVGeJ2LNDDem+NJ7wTTeDvadVwEwdEa1xHwSxBw+B8MYN4PMgVkjJUu0g3Sw3x5fxivfRFC+zY+bR0g4cK+PAjmFHp/sxIF7fZgxXET5Nj9ePRxN27bE28FXF1R0xTj+1K1SIKdAQOZoCGg4H+M4eEFJeztIF+opQ/+IuvpgBOV5AtbM8Njy7GwljQENqw+GUe/XsGaGO2UfZePtQNb0oW5uMbPutMtG04eJEBhS2g7SzdahfCqkYfXBCL7oVLBmhgdLJtrjPz1TvHtSxuqDYdw+Sv8oOyHHnJNlvB3saJMhc4agwmlom0U4BCDfIcAlcMwf4zC1HVhFdu/dZaw9FkX5tgCKvQKOPZBHgWxBSyY6cOyBPBR7BZRvC2DtUeM/yq49FkXZtgD2dKrokjk6IhoFsoXIGtAZ1dAZ5djbqaDMpHZgJbbrKbdHNCzfG0ZMA35ysxvTh9Fg40zw9UUVPzoQgVMANlV4UOQeWn8i3g6OdKloCmhwCDTWOBNIAsMUn4DyPMGQdmBF2bdHl/FBq4JZ1d2YPkzE7+d5KZAzSO//s1nV3figVUn6tT5oVXDrhwEcuKDiTJhD7ZkcnlifonG0hDQcuKDi1g8DQ2oHVmWbnvLLX0ewsTGKTRU5eHA8lSoy2dbTMpbvDWHFFBdenO6+pt99+esI/ulYFLLGoXDqHWcyr8TgFIAfTr32dmBlWR/KbWH9Y6qqcWy6PQfjs/wigV2cDmlYvjcEkTFsqvBgzBUmgoq3gyNdKk6HNBpVkSUcAlDsFXBDvnhV7SATZP4eXMb2Fhmzqrtxy3AR27+XS4GcRcbnCNg+Lxe3jNDLGdtb5Es+d3uLjFs/DODQBRUtFMhZRdaA5iDHoYt6OeNy7SBTZG1P+ZVvIni9IYZNt3vwgE3GN9rV+y0yln8RxtNlTrwwre/H2Fe+iWDt0SgiKocGKldksxxRXxj22etdA9pBJsnKUH7uyzA+P6viN5U5GEe9Y1toCWl4uC6EuwpEvHarB4DeDrY0yzgf1RCi3rEteESGAjfDwxMdiXaQabIulJ/cE8KZMMdvKnOQI9EyNHYSUjgergthtEf/f99xRoFf5pZZhJSkRo4IFLgFzB8t4V/uyEn35lyzrAlllQMP1wWRIwK/nO1N9+aQNFE5cN17fnRGOQCOMPWQbckjAiNdDLNGSvhNpReZtExgVny274xy3L2jG+M8jALZxjqjHPM/6QYHoHAKZDsLq0BHBPiiU8X8T7p7TtKZIeN7yo0BDUs+68aD4x14eUZm1pDI0DUGNDz0aQDnYsBFGYjSBT3So8jNMNIJvDfPlxGTjWV0KO89p2LJZ0E8e70LP5zqSvfmkDTZe07FQ7VBxFSOoEp355GBRrgYXALDe1VeVIy09p28GRvKjQEN8z/pxsKxEn5xe+YV84kxGgMaKj/qxsWYBoExhCmQySW4BGCYU0DdglxL95gzMpT12mEAT5RQD9nOOqMcc2r8OBXiUDgQ09K9RcTqckRgQg7DzoV5GOWy5tU/654uLiE+yuLB8Q4KZBtTOfBQbRBtEYCDUSCTqxJSgfao3nas+qEq40L54bogpuULdFHP5pbUBVHvVxHTQCULck0uxjjq/SqW1AXTvSmDyqhQfnJPCDkisGEW1ZDt7Mk9Iew+qyCg0EU9kpzOKMfnZ1U8uSeU7k0ZIGNC+bkvwzgT5jQO2eae+zKMD1sVXIjRsDcyNBdjHL9vkfHcl+F0b0ofGRHKr3wTScxlQezrlW8iePtPMXTLHDGNApkMTUzj8MvA23+K4ZVvIunenATLj77Y3iLjb74IY++iXJpcyMa2t8h4dGcIkqD3cAgxSo7EIDGGt2d7LLFitqVDuS2sYVZ1NzZW0PSbdtYW1jD99wEEqYZMTOIUGHwO4Ov7fGmfKN/SXc/le8P4q1InBbLNLdsdQrfMKZCJaWIaR1DhWLY7/Rf+LBvKL38dgco5XsqitbfItXv56wj2n1fBmDUH+pPsEVGB/edVvPx1euvLlgzlD1qVxCKnxL4+aFXwsyMRxDQqW5DU6Ipx/OxIJK2rZFsulNsj+oKYmypokVM7a49oWLY7hJgGBGQKZJIaCgeiql4ya4+k5zZRy6Xe8r1hLJvsxIPjqY5sZ0/sCSMgc8h0+zRJMYXrHYEn9qRn/LKlQnnt0ShiGvDyTVRHtrO1R6P4Y2f6Pj4SElE59nYqWHssmvL3tsyQuFMhDeXbAvhiUS6mD7P2fKfEPKdCGia/54dXYuiisgVJI7fIoHKg6UEfJqSwlGqZnvLqgxE8d4OLAtnm/nZ/GBoHBTJJu4jKoXKOv92f2jKGJUJ5e4uMLzoVKlvY3PYWGR+1KXBm0iqXJKtpHKhpU7C9RU7Ze1oilFcfjGANTcVpeyv3hSFrdJMIsRZZ09tmqqQ9lF89HEV5noAlE2m0hZ29ejiKM2GOCK1ATSwmqnK0R3jKJi1K64W+xoCG8m1+1C/Os/SaWcRc8XbgFvWl4QmxGofAoGg8JVmV1iRcfTCCNTd7KJBt7of7I+CgQCbWJWscHMCqFJQx0paG21tk1PtV/P2NtM6enW1vkbGzQ4abBt2QDLDrrPkX/dIWyuvrY3iWFj61vX84qE88RbVkkgmiKsc/HDS3tpyWUN5xRkFLSMXjk53peHtiETvOKDjmVxGkm/dIhohqQL1fxY4z5jXatITyumNRrJpKY5LtTu9x0JhkklkUzkztLac8lHedVXC4S8VTpdRLtrNdZxV8dUGlMckk48gax5fnFew6a05vOeWhvL4+Rr1kghcP6bVkQjKRCoYXD5nTW05pKO8/r2DnWQUry6mXbGf7zyvY2aHQtJwkYykax66zCvafN763nNJQXl8fw8oyCmS7+2+H9HHJhGQyVdPbstFSFspHulS8f1qm0oXNHelS8XGbCpHW3CMZTuHAx20qjnQZO54zZaH81vEYlk9x0U0CNrf2aAQap0mHSHZQOcfao8b2llMWyltOylhaTJMO2d1vTioQqJNMsoTI9DZtpJSEcnWrjDEeATOGUzfZzqpbZYRVIEYX+EiWiGlASOWobjXu1uuUhPKWZhlLaWpO21t7NAqRUdmCZBmut22jmB7KEZVT6YIgonL8W7tCc1yQrKNw9LRtYzocpofylmYZC0dLGO2h6Tnt7Jd/ikGjTjLJUhrX27gRUhLKj0yiscl2t6EhRmvvkawlML2NG/JahrzKJTQHNew+q1DpwuaagxqOXNQQpWFwJEupHDh8UUVzcOhXsU0NZeolEwDY1BgFp3v4SNZj2NQ49At+pobyh60yHppAvWS7+3WzDCddUiBZTmB6Wx/y6xiwLYOKqEBtu4KqQhqbbGcRFTjerdH6eyTryRrH8W5tyCOMTAvlT9tlVBVJcNPFHVv7qM3c9cwIsZqhtnnTQrm2Q8G8IsmslycZ4u0TMZrvhNiGwPQ2P6TXMGhbBqhtV1FVSKFsd3V0wwixEY0Dn3UMrcGbEspdMsdXFxRUUU/Z1rpkjjMRGnVB7KU9rKFLTr7dmxLKn7YrqCqUaElMm/t9C9WTiQ2xobV9U0K5tl3BvCIaCmd3W07IkGieTmIzDHrbT5Y5odxBpQsC7D6nQKYJL4jNaBz4/FzydWXDQzmgAA0BDXeOokvudhZQgHNRCmRiT+ejHIEk5743PJTru1SU+ej2Lbv78pwCTplMbIpzji/PJZfKhqdnQ4BCmQB1Z2VIVE4mNiUx/RhIhgmhrKE8n0oXdvfVBQ2MLvIRm9LA8NWF5GaMo/IFMcXRLhUxmqqT2JTKOb65kNzFPlN6ymU+6inbXUuIApnYW1uSN04ZH8p+DWV51FO2u25jV10nJON0J3lXn6Hp2RzUMNzFkOegWqKdNQc1mtSeEIakViIxNJQb/FRPJsDODoVusSe2x6AfC9fK2FAOaBTKBHvOqXBQMyA2x6AfC9fK0EOnLcwx3ksX+eyu0a+CqheE9BwL18jQUA7IHLmUybZ3PsYRHfqivoRkNJUDZ5OYasDYUFY4fHSRz/Yu0oydhAAALsbSHcoyhTIBgkOY4JuQbJLMqjsUyhawefNmMMawc+fOdG+KISJ0J19G2L0wF/yxYaikZdtMk8yxYGgodyuAj2ahsb0ozaFMCIDkQtnQUyTVlAkAKJyBhl9Y35013QMea1ichxFOhlHvdl3V4+TylCQOA+NHX9AnIduj6gUhumSOBdvUlON12+PHj6O0tBSMMTDG8PTTT/f5OWMMpaWlg75G/Ofxr9527tyZqAsvWrQo8ZxFixb1+flgv9vbYL+b7HbE9zP+Or330cwatmqz8sWG2zzgjw1LfDUszuvz861V3j4/548N6/PzykIJ/LFh2HCbJ1Hn5Y8NQ+eS/D4/j3/1rgGvLHeBPzYMK8tdaFicN2Ab4j8f7H3j21VZKCWeN8UnYKSLJZ7/2T25gz6+4TbPJfdva5V3wL9R/32I71tvvfe9/7ZmqmQOBduEclxJSQnefPNNcM7xzjvvYOPGjWCMJR7jnKOpqalPIB4/fhyMMaxYsSLxnBUrVgwarpWVlVi2bBk456irq0NNTQ0YY4nHOOcoKSkZNPh7/y7nHDU1NUPajjVr1oBzjurqauzcuROPPvoo6urqEr+7bNkyY/5R+7FTJG+4zYMflLvw8/oo2NsXwd6+iHNRLRFauxfmYvF4B+Z+3J34eWNAGzR0flDuQkeEg719EXM/7k6E4Gf35Pb53c/uyR3wu+tu86CmVQZ7+yJW7Qtjik8Af2wYXpzu7rVdfNAwBID1PdvfGNBwLsoTvxPf7v6PP7MvPOj+zf24G4vHO/oE88pyFz67JxfbTsuJ3z8f67stW6u8uGOUlPj5qn1h7F44cD8zTTLHgu1uhn3nnXcwZ84cAMAjjzwCAFi4cCGqq6sTz1mxYgVqamoS37/22msoKSnB66+/nngs/vef/exnfV7/pz/9aeJ158yZg5KSEpSUlODbb79NPOepp55CU1MTjh8/fsnfjX9fU1OTeN61bMfChQv7vNbp06cT2xTXe5tIch6d5MSeTiURUoBeq31mXxgry124Y5SEn9dHUddrDoQn94QAYECPck+nggdrgwCAug4FjQH9Dhz29sXEc2pa9UHgK8tdfX5322k5sQ3r66M4F+U4F+V9asC7zioY6WKGjbaoLJQG7F9dh4Jtp2UsHv/davY/KHehMaAl9g3Q/w1Gulji5FXoZon9je/DYDVvOzA0lH0OhoDFx6iOHz++z/clJSWYPHlyn8cmTZoEAIkw/Oijj7BgwYIBr1VSUoITJ070eWzixIl9vi8tLR3QK44/p7W1tc/jd91116Df792795q3o38vuKKiAgAuWzoh16ayUMJIF0PHJebNnT9aD79fn+x7N01dh4JzUY7r+63Q0/91zkW1AYvPxoNrSr85Zk72m43sfIzjfL8bF+LPuWW4Mbfd/sVEPXj771/8feLhP8Un4GhX3wG78RCf6NX3Y985FVN8woDSjx0ZelkuHsqjXNl14Dc1NWHjxo3YuHFjxm7H5MmT0dTUhJKSkkQw19XV9ek5G0VgDJqNVk3tH4j91Q0yU1j/wMxE8UAdrJwSFw/mxeMdl60Tx3v5P+ipbQN9PyFkqmSS0PBQztbJzVesWNGnbJBKvXv3Q9mOyZMng/eEZWlpKSorK9HU1DTgk8JQCSy5CxzZqrJQGhDMI5wsK4IZAOZ+3D3oiQf4LpS3nZb7lC8G88y+MJ7ZF0ZloYTP7slF55L8jB+Cl8wylcaWLyTrly+SsXDhwgH1XzN8/vnng34/duxYw7fjzTffBDCwhGIEMbs+KF1SvAxx28jBywE7zuhBFf+YHxcve/T/SG8F5y4xk9Rgj8f373LlkPi/UaH76htFvC49Mgs+cSdzLBgayrmSfgNJtlm9ejVqamoGXEwrLS01dFjZ888/j82bNwPQ69nPP/88VqxYkejJDmU7nn766cRrA8CvfvUrAN8FvpFEG42/2HVWwR2jpD4X3nYvzMWG2zxYXx9FY0DDD/pdlPvdXC/ORfkVe47p0BHhg14MHOzx+P6t6zU8DtBHpPSuDb9zIoY7Rkl9htEB6DO8r3NJfp/Xnl0gDainZ6JkQtmUmnK2mTNnTqIe+/zzzyceN7om29TUhAULFuDRRx8FMLBUMZTteP3111FaWpp47fj7GV26AACnyBCyyR0kD9YGsbXKi3W3eRLh1BjQ8Mw+feRA2TZ/YvxwXGNAQ9k2f1q290oerA2ic0l+ok68al8Y6+ujl3y8bJs/Mb44rv/+PbMvnAjv3ieo3mWPf/9ZsE9tuv/IkUzlTKJ+wTg37orM9/eEUFkg4YkSp1EvSTLQmN/6cSZMEyoTUuhmaP/zwceGXwrVlInhaFIqQnSeJOoXho9T7rbetQuSYj7HlZ9DiB0Mc6Y5lMd4GE4HKZXtriALrpoTYoS0h3KZT0BDgGqJdjfFJyY1PpOQbCIwYHr+td89aWwo54kUygR3FEiw0Q19hAyKc/1YuFaGhnKxV8CFKIefLvbZ2pwCO41UJmRwHPqxcK0MnyWuLE9Ag596y3ZW7BWSuuefkGzCoB8L18r4UKa6MgGQQ8PiiM15kjwGDA/l8nwRDQEagWF32TBvASFDkexsmab0lOstONEKSa0ZBs3ZS0imuiE/uXg1IZRpBAYBKkbQsDhiXwzA7FHJTS1kUvmCQtnu5hY5aAQGsS+mHwPJMDyUfZJewtjdmaWz3ZOrcssI0V4rqBLSG+85BpJgysKpVYUSatuprmxnPgnIs/jK5oSYJc/B4EtyYmRzQrlIwqft8pWfSLLaXUkMnCckG8y6xGo0V8OUUJ5XJKG2Q6FbbW3uP11H82oTe/r+EOaUNyWU8x0MtwzXg5nY173jHHRnH7EdxvS2nyxTQhkAqopECmWby3ewpKYuJCST5Yp620+WeaFcKOHTdgplu5tXZOgykIRY3tzCobV500J5XpEDte0KIjZZQJMM7okS15WfREgW+Zsy95B+37RQdov6KIzaDhoaZ2f3jKaeMrGXobZ500IZAP5srAPvnaKhcXbmFoHRHlObGSGWMdojwD3EkaCmHi1Lix3YfCJm5luQDLBssgOMrvcRG3h00tBXDTY1lIu9Au4skCiYbW75FBcNjSNZjwFYVT70ayimf65cWuzAlpNUwrCzYq+ASUmswEBIJpnoFZJaaaS/lIRyTZuCM2GaOc7O/u5GGoVBstt/ud6YNm56KLtFhqUTHdjSTL1lO/uP1zmphEGyFgPw16XGTCuQks+US4sd2NxMdWU7c4sMs2mCIpKlZo4U4RaN6XakJJQXjXXgTITj4AUas2xnL0xzU2+ZZB0G4Mc3De2Gkd5SdvWFShhk0VjHkMdwEmI1TkFv20ZJWSg/PtmJTY1RRKizbGtLJhrXeAmxgocmGNumUxbKN+SLeGC8A+uORVL1lsSCfnSjO3WNjhCTCQx4cbpxpQsghaEMACvLnVjfQBf87OyGfBF3j6H5MEh2qBgp4oZ8Y2tyKQ3lmSMkzCmQsL6egtnOXr3ZDYMuVBOSNiIDfj7LY/jrpvyT5MpyJ5UwbG7mCAm3JrnSLyFWcdMwETNHGP+pL+WhPLtAwo35It74lnrLdvY/ZnogUG+ZZCiRMWwwoZcMpCGUAWDVVBf1lm1udoGEaQbX4ghJlVIfw+wCc66NpCWU54+WMC5HxFvHqbdsZ2tneiBRd5lkGJEB/zwrx7TXT9vopJXlTqw9Fk3X2xMLmD9aQkkuhTLJLNflCphv4oo6aQvl+8c5UOYT8OphCmY7e+1WDxw0cJlkCIegXw8xU1oPh1du9mD1gTAaAzStp13dP85hWm2OEKPdOkLC/ePMvSs1raE8xSfgxzPcWH0wnM7NIGn2xu3m1ecIMdL/vcv8tpr2D44vTHOj3q/hXVqdxLam+ASsnuamIXLEshiA1dPcmOIzPzIZ55yb/i5XsL1FxnNfhnHsgbx0bwpJo8L/58fZCJWyiPUMdzKcfzg/Je+V9p4yoNcVbx8l4cVDNHbZzv73HR66/ZpYjsiAjRXmXtzrzRKhDABrZrjx2pEovr5Ic3va1f3jHFgwhqb2JNYyp1DC0mJjlnq6GpYJ5Qk5Atbc7MaPDlBv2c7+5+0e6zRKYnsCS83FvT7vmdJ3u4Jnp7rgFEBlDBubkCPgH2d6IFEZg6SZyICXb3JjQk5qY9ISF/p6a49omFXdjQ23efDgePooa1ff+6Qbn7Yr6d4MYlOMARUjJexZmJvy97ZUTxkAitwCNlXkYPneEE6H6Eq8XW2ekwOfg7rLJD08ArC1Kj3j5y0XygBw71gJK6a4sPyLULo3haRJkVvA5jleSJZsoSSbiQz41ztzUOROT+OzbJN/cbobosDwEtWXbevesRL+6/UuGiZHUoYBWFnuwl+kcLTFgG2wWk25t7awXl/eWOHBAybfb06sq6I6gD+eo6GSxFwMwIzhAr66N703sVm2pwwAYzwCNlV4sPyLMFqovmxbW6u8yKXhGMRkbhH44Hupv7DXn6VDGdBvKHi6zImH60IIKZbt1BMTjfEIeGdODg2TI6ZxCMCvK70Y40l/JKZ/C67CC9PcuKtAxMN1dOHPru4f58BLN7nhoFmLiMEcAsPzN7hNn5Lzalm6ptzfk3tCiKocv5ztTfemkDR55o9hbGyMQs2YVkusTGTA90tceOP21M1tcSUZFcoA8B8+C2Kch2GDiWtkEWtbujOEd0/GoGVUyyVWIzBg4RjJEnXk3jIulFUO3L2jG5UFIl6eYZ2zG0mtyo+6sfMs3fFHkiMwYFaa7ti7koyoKfcmMuA3lV5sPS3jn2jhVdv69J5cTBsmpnszSAZiAEp8InYtsF4gAxnYU45rDGiY/0k3Fo6V8AtaTsiWOqMcMz8M4GSQhkuSqzfGI+DQfT6MclnzonHG9ZTjpvgEvDvXi5pWhXrMNjXKxfDJ/FwMd1rz4CLWk+cAau/JtWwgAxkcygBQMVLEjrtz8a9NUbxIi6/a0hSfgOp/l4uRFj7IiDUMczJ8PN+XknX2hiJjyxe9dUY5Hq4LYlq+QKMybKoxoGHBjm6coFIG6UdgesniD3fnWj6QgSwJZUAflfFwXRA5Imgcs011Rjnu3tGNgxdongyiE5j+aWrnAuvWkPuz/mnjKokM+O1cL1wiw31/CNIt2TaUIwJjPQKK3Jlx8BFzCT0T1R++PyxQe1cAAAeXSURBVC9jAhnIolCO+5c7cnB9voC7dwRpEiMbaQlpuHtHEDfkCzjz5/l4aIKD5sqwMannxpDdC3MzburXrAtlAHjtVg/uGyehorob77fI6d4cYrL3W2RUVHfjvnESXrtVv6Hod3O9WD7FBSfNlWE7ToHhiRKX5e7Uu1pZU1MezPYWGcv3hvFXpU68NN2d7s0hJnjpUAT/qymGTRWeQSeUeeWbCH78TRRRmizDFvTJhVz48YzMPd6zOpQBfaL85XvDUDnHpoocjE/xyrTEHKdDGpZ/EYIoMGyq8Fx2ysXtLTL+cmcQAborO2sJDPCKwK/meC0z21uysj6hxngEbK3yomKkhFnVAWw9TeWMTLf1tIxZ1QFUjJKwterKc+DeP86B+sV5qCqUQNWM7CMw4LYREuoX52V8IAM26Cn39kGrguV7Q1g22YmXb8rcjzd29uKhCN48HsOmihzcO1a65t//+wNhvHY0CpmuAWcFiQHPlLuwdmb2TE5mq1AGgPaIXs6IacBPbnZjOk1qkxG+vqjiRwcicArApgrPkFYa/qBVwV/uCqIrZqumn1UEBvgcDG9UeNK6yKkZsr580V+RW8Dv5noxv0jC7dXdeJFWy7a8Fw9FcHt1N+aPlvC7ud4hL/1+71gJ9Q/48L0iCQ7bHQGZzy0y3DVK/z/MtkAGbBjKcc9e70L9Yh+agxqmvu/Huyep1mw1756UMfV9P5qDGuoX+/DsVJdhr13kFvBvd+fiJzd7INn2KMgsItPLFaunuVC3IHfIJ2ersl35YjDbW2SsPhhBeZ6ANTM8GXF/fDZrDGhYfTCMer+GNTPMXzvtVEjD03tDqGlTqNZsUS6R4Z7REl6v8GBClo+gyu69u0r3j3PgwL0+zBguoXybH68epqlA0+XVw1GUb/PjpmEiDtzrS8nV9Ak5At6fl4vfzvVirIeGZ1iJyICxHob/c2cO3p/nzfpABqinPIDeS4ug3q/i2akuPD45+2pWVvTW8RjWHouizCfglZvT+2nl1cNRvHCApoJNJ5Hpk4w9d70b/3irvUZKUShfwvYWGevrY2gJqVg11Y2nSimczfDGtzGsOxbBuBwRK8udlhln2hjQ8J/3hrC7U0E33XSSUm4RqCyQ8M8VObYsJVIoX8GOMwrWHYvicJcezivLKZyNsL5eD+Mb80WsmurC/NHXPuY4Fba3yHjpUARH/RrNPGgyt8hwY76Al24y/zqClVEoX6VdZxWsr49h51kFK8ucWDXVDTcNcb4mERVYdyyC9Q0xzCmQsLLcidkF1gzj/nacUfB3B8L48rwKmkbDWC6R4aZhAv77zR7LnpxTiUL5Gu0/r4fz+6dlLJ/iwtJiB2YMp3S+nIMXVGxplrGpMYoHxjuwstyJmSMy8+DbdVbB6oMR7OlUEaF0HhKHAMwukLBmhjtjTs6pQKGcpCNdKt46HsOWkzLGeAQsnejA0mIHRl9hHga7OBPWsKVZxubmGM5EOJZOdODxyU7ckJ8dJ7D95xW8dCiKmjaZhtFdA5EBjDEsGiPhpZtcGXtyNhOFsgGqW2VsOSlj84kYFo1x4JFJTiwttmdNbPMJ/URV06YkTlSLxmbvv0X85PyLphgCMqeAvoQcEXBLDI9f58Rfl2bPydkMFMoGiqg9odQcw55OFY9McuKhCQ5UFYpwZ9ryB1cponLUdqh475R+UrqzQMLSYgeWFjttV3OvbpWxrj6Gj9tkqjsDPSt+MNw3TsKKKc6sPjkbiULZJCe6NWxpjqG6TUFtu4KqIgnziiRUFepfLEMzmnOgtkNBbYeCT9u/27dFYyQ8MsmFYm+G7piB4ifnDQ1RfH1RtVXvWWSAwBhuGS5g2WQXniix38l5qCiUUyCiAp+2y3qYtav46oKCqkIJ84ocqCoScecoa9fVdnfq2x3fh1uGS6gqEhP7QAfdpcVPzr88IeNwlwq3wBDOsm60R2SIaMCMYQIemuDAssl0ch4KCuU06JJ5opdZ26GgIaChzCegzCegPF/s+buIsjwBeY7UNG6/zNHg19AQ0NAQUFHfpfb8Xd+2qkIp0dvPT9E2ZZv4yfmjNgW/OyXjZFAD67lzLZOITP/ENDaHYcEYJx6eSCdnI1EoW0BAQU8I6kGYCES/huEulgjs8V4RuaI+j2ziS9L/zJW+exwAAjJHQOboVnr+rvDEYwGZo1sFTge/C94LUY6yvIEnhvJ8ET5rd+QzVvzk/FGbgupWGadDHJIAS92kIjJ9HLGs6XNQVBZKWDLRQSdnE1EoW1xzUEODXw/PtjAfELDdCgYELjAwuHuHdjzIx3h6Aj9PRLGXhvKlW++T895zKr48r+LbgIpz0e8O0ZgJ9WlJABwM4GBQOUeRWz85T8kTMbdQpJNzilEoE5IB+p+cW0Ia2iMcXYmTM0dUBUKKhpgGxLjei3UyDo8kwCUALhHIlRhGOBmGORlyJGCsR8B1uQKdnC2EQpkQQiyETouEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIhFMqEEGIh/x8CSRxFLErw3QAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/mysql-join-right-join.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "c628d0a7-23a1-42c4-bd42-a2325aa7d6dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT \n",
    "    member_id, m.name AS member,\n",
    "    committee_id, c.name AS committee\n",
    "FROM members m\n",
    "RIGHT JOIN committees c \n",
    "    ON c.name = m.name\n",
    "    # USING (name)\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "2e7ffb4b-9542-4185-af79-0622e3f17f08",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=(pd.read_sql_table('members', engine).add_prefix('m.')\n",
    "     .merge(pd.read_sql_table('committees', engine).add_prefix('c.'),\n",
    "            left_on='m.name', right_on='c.name', how='right')\n",
    "     .rename(columns = {\n",
    "         'm.member_id': 'member_id',\n",
    "         'm.name': 'member',\n",
    "         'c.committee_id': 'committee_id',\n",
    "         'c.name': 'committee'})\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "660e1569-9aa6-4650-a083-7daf4f875f00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "71b4ade3-a1c4-4ff1-b943-0833b199cacd",
   "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>member_id</th>\n",
       "      <th>member</th>\n",
       "      <th>committee_id</th>\n",
       "      <th>committee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Mary</td>\n",
       "      <td>2</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Amelia</td>\n",
       "      <td>3</td>\n",
       "      <td>Amelia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>Joe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id  member  committee_id committee\n",
       "0        1.0    John             1      John\n",
       "1        3.0    Mary             2      Mary\n",
       "2        5.0  Amelia             3    Amelia\n",
       "3        NaN    None             4       Joe"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "63c5205b-dcaf-458f-82ca-28fb1ba6df33",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT \n",
    "    member_id, m.name AS member,\n",
    "    committee_id, c.name AS committee\n",
    "FROM members m\n",
    "RIGHT JOIN committees c \n",
    "    # ON c.name = m.name\n",
    "    USING (name)\n",
    "WHERE member_id IS NULL\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "177440ea-6c88-47d7-a155-551054e6aa44",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=(pd.read_sql_table('members', engine).add_prefix('m.')\n",
    "     .merge(pd.read_sql_table('committees', engine).add_prefix('c.'),\n",
    "            left_on='m.name', right_on='c.name', how='right')\n",
    "     .rename(columns = {\n",
    "         'm.member_id': 'member_id',\n",
    "         'm.name': 'member',\n",
    "         'c.committee_id': 'committee_id',\n",
    "         'c.name': 'committee'})\n",
    "     .query('member_id.isna()')\n",
    "     .reset_index(drop=True)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "36040076-8492-4b86-b307-bab5047c3d10",
   "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>member_id</th>\n",
       "      <th>member</th>\n",
       "      <th>committee_id</th>\n",
       "      <th>committee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>Joe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  member_id member  committee_id committee\n",
       "0      None   None             4       Joe"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "a7d780b5-07fb-4505-85da-5596f606f0ee",
   "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>member_id</th>\n",
       "      <th>member</th>\n",
       "      <th>committee_id</th>\n",
       "      <th>committee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>Joe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id member  committee_id committee\n",
       "0        NaN    NaN             4       Joe"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68026bd8-db31-48a6-88ac-754710e1e213",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL CROSS JOIN clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1efa16f-97c7-41be-b5b8-29205d717956",
   "metadata": {},
   "source": [
    "syntax"
   ]
  },
  {
   "cell_type": "raw",
   "id": "005afc50-410f-4208-bb90-92c9ba1f8a1f",
   "metadata": {},
   "source": [
    "SELECT select_list\n",
    "FROM table_1\n",
    "CROSS JOIN table_2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "94bfbd55-7abb-4e52-96c2-738c65b32089",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    member_id, m.name AS member,\n",
    "    committee_id, c.name AS committee\n",
    "FROM members m\n",
    "CROSS JOIN committees c\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5e0846c9-50a3-4db3-9afe-6a886fe48001",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('members', engine).add_prefix('m.')\n",
    "    .merge(pd.read_sql_table('committees', engine).add_prefix('c.'),\n",
    "           how='cross')\n",
    "    .rename(columns= {\n",
    "        'm.member_id': 'member_id',\n",
    "        'm.name': 'member',\n",
    "        'c.committee_id': 'committee_id',\n",
    "        'c.name': 'committee'})\n",
    "    .sort_values(\n",
    "        ['member_id', 'committee_id'],\n",
    "        ascending=[True, False],\n",
    "        ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "17f9761a-c379-4b76-a46c-ad4f9944832d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "44a3da22-cedc-428e-933c-9d53388ee299",
   "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>member_id</th>\n",
       "      <th>member</th>\n",
       "      <th>committee_id</th>\n",
       "      <th>committee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>4</td>\n",
       "      <td>Joe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>3</td>\n",
       "      <td>Amelia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>2</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane</td>\n",
       "      <td>4</td>\n",
       "      <td>Joe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id member  committee_id committee\n",
       "0          1   John             4       Joe\n",
       "1          1   John             3    Amelia\n",
       "2          1   John             2      Mary\n",
       "3          1   John             1      John\n",
       "4          2   Jane             4       Joe"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "d6cf572e-8110-44fb-b027-32d95a1ee4cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f61de2ce0d0>"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Delete the tables\n",
    "engine.execute('DROP TABLE IF EXISTS members, committees')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}