MySql statement to select based on a column in an intermediate table

advertisements

In a database, I have a username table and I have jurisdiction table. I also have a intermediate table to assign a user to one or more jurisdictions.

employee table

  • userID (primary Key)
  • firstName
  • lastName

records:

+--------+-----------+----------+
| userID | firstName | lastName |
+--------+-----------+----------+
|      6 | John      | Doe      |
|     11 | lisa      | lopez    |
+--------+-----------+----------+

jurisdictions table

  • jurId (Primary Key)
  • region

records:

+-------+--------------+
| jurID | jurisdiction |
+-------+--------------+
|     1 | California   |
|     2 | Texas        |
|     3 | Washington   |
|     4 | South Dakota |
|     5 | Alaska       |
|     6 | Ohio         |
+-------+--------------+

user_jurisdiction

  • userID (Foriegn Key pointing to employees userID)
  • jurID (Foriegn Key pointing to jurisdictions jurID)

records:

    +--------+-------+
    | userID | jurID |
    +--------+-------+
    |      6 |     2 |
    |      6 |     3 |
    |     11 |     2 |
    +--------+-------+

I've been trying for hours to come up with a sql statement that would select/list all the workers from "Texas". I've been using many altercations of this sql statement but no success:

SELECT  jurisdictions.jurisdiction,
        employees.firstName
FROM    jurisdictions,
        employees
        INNER JOIN user_jurisdictions
            ON  user_jurisdictions.jurID = jurisdictions.jurID AND
                user_jurisdictions.userID = employees.userID
WHERE   jurisdictions.jurisdiction = "Texas";

But I have had no success. What sql statement would get a list of employees that are involved from jurisdictions.jurisdiction = "Texas";


What you are doing right now is you are producing catersian product from tables: employees and jurisdictions. The proper syntax of joins is to explicit define the type of join between two tables.

SELECT  a.*, c.*
FROM    employees a
        INNER JOIN user_jurisdiction b
            ON a.userID = b.userID
        INNER JOIN jurisdictions c
            ON b.jurID = c.jurID
WHERE   c.jurisdiction = 'Texas'

OUTPUT of the current query

╔════════╦═══════════╦══════════╦═══════╦══════════════╗
║ USERID ║ FIRSTNAME ║ LASTNAME ║ JURID ║ JURISDICTION ║
╠════════╬═══════════╬══════════╬═══════╬══════════════╣
║      6 ║ John      ║ Doe      ║     2 ║ Texas        ║
║     11 ║ lisa      ║ lopez    ║     2 ║ Texas        ║
╚════════╩═══════════╩══════════╩═══════╩══════════════╝

To further gain more knowledge about joins, kindly visit the link below: