MySQL – Select Rows Mapped to Junction Table in Many-to-Many Relationship

many-to-manyMySQL

I have three tables:

  • locations (has zipcode, location name);
  • professionals (has name);
  • location_professional.

I want to select only those rows from locations table where professional_id is 4. What's the best way to do this?

Best Answer

Assuming that locations and professionals also have their PK columns as location_id and professional_id, respectively, and location_professional uses those columns to link the two entities:

SELECT loc.*
    FROM location_professional AS lp
    JOIN locations AS loc  ON lp.location_id = loc.location_id
    WHERE lp.professional_id = 4;

In your simple example, there is no need to also JOIN professionals AS p ON .... In a typical case, that would also be needed.

Related Question