I have 3 tables:
States, Municipalities and Localities, each with their ID, and I want to relate them to obtain to which state and municipality the localities belong, but the detail is that I have the data in the following way:
When generating the join I join each id state with each idLocalidad, and I wish only to obtain the corresponding, I hope you can help me.
I would design my tables like this:
estado : estado_id, estado, pais_id
municipio : municipio_id, municipio, estado_id
localidad : localidad_id, localidad, municipio_id
Unique indexes:
estado would be the columns: estado and pais_id (since two states could be called the same but belong to different countries). municipio would be the columns: municipio and estado_id (since two municipalities can be called equal but belong to different states) localidad would be the columns: localidad and municipio_id (since two locations can be called equal but belong to different municipalities) You would know the state to which a locality belongs using the municipality to which it belongs.
Example of query:
SELECT e.estado, m.municipio, l.localidad
FROM localidad l
INNER JOIN municipio m ON l.municipio_id = m.municipio_id
INNER JOIN estado e ON m.estado_id = e.estado_id
The column estado_id is not needed in the table localidad ... you can get to the state by your municipality. And even if you have inherited a design in which the estado_id column is included in the localidad table, you can ignore that column thinking about future optimization, as long as the relationships between municipio and estado correct.
If the countries were needed, the same criterion would be applied, the only one that would refer to the country apart from its own table, would be the table estado .