Easy to come up with the SQL then you solve it in Pandas 🏆
MySQL:\nThis problem can easily be solved using the IN clause. Just adding a little bit of salt to it by using NOT in front of it does the trick.\n\nSQL []\n# W
# MySQL:\nThis problem can easily be solved using the **IN** clause. Just adding a little bit of salt to it by using **NOT** in front of it does the trick.\n\n``` SQL []\n# Write your MySQL query statement below\nSELECT s.name\nFROM SalesPerson s\nWHERE s.sales_id NOT IN (\n  SELECT o.sales_id\n  FROM Orders o\n  JOIN Company c\n  ON c.com_id=o.com_id\n  WHERE c.name=\'RED\'\n)\n```\n\n# Pandas:\n`pd.merge()` will merge the tables orders and company. Our merged table will look like this.\n\n| order_id | order_date | com_id | sales_id | amount | name   | city   |\n| -------- | ---------- | ------ | -------- | ------ | ------ | ------ |\n| 1        | 2014-01-01 | 3      | 4        | 10000  | YELLOW | Boston |\n| 2        | 2014-02-01 | 4      | 5        | 5000   | GREEN  | Austin |\n| 3        | 2014-03-01 | 1      | 1        | 50000  | RED    | Boston |\n| 4        | 2014-04-01 | 1      | 4        | 25000  | RED    | Boston |\n\nNow filter the red order rows with `name = \'RED\'`.\n``` python3 []\nred_orders = df[df[\'name\'] == \'RED\']\n```\nThen using the unique sales_ids, filter the sales persons.\n\n\n``` python3 []\nimport pandas as pd\n\ndef sales_person(sales_person: pd.DataFrame, company: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:\n  df = pd.merge(orders, company, on=\'com_id\')\n  red_orders = df[df[\'name\'] == \'RED\']\n  invalid_ids = red_orders.sales_id.unique()\n  return sales_person[~sales_person[\'sales_id\'].isin(invalid_ids)][[\'name\']]\n```
['MySQL', 'Pandas']