Good day! Over the past 7 years of work, I got so used to the framework framework for working with the database that I have forgotten how to write sql queries (In general, I have 3 tables:

1: regions:

regions

2: cities:

cities

3: organizations: There are a bunch of fields in this table, but in this case I'm interested in the fields: region_id and city_id

I need to write a query that displays all the cities, but only from the region whose id is stored in the region_id field of the organizations table.

I got the following request:

SELECT c.id, c.name FROM cities c, regions r, organizations org WHERE c.region_id=r.id AND r.id=org.region_id

The request returns cities correctly (cities that are in other regions do not fall into the output), but 3,500 lines are returned from the base, even though I have only 7 cities in the list .... The list of cities is duplicated many times ... enter image description here An elementary task ... Entered me into such a stupor .... Prompt, pliz, what's the problem ..

  • The number of lines you have apparently the number of organizations. or select distinct or as already answered below, region_id IN(select ...) - Mike

2 answers 2

try this

 SELECT c.id, c.name FROM cities c, regions r, organizations org WHERE c.region_id=r.id AND r.id=org.region_id AND r.id=c.region_id

    Extra multiplication of tables. Correctly like this:

     select c.id, c.name from cities c where c.region_id in (select r.id from regions r, organizations org where r.id=org.region_id)
    • Practically, only the regions table in the query is not needed at all, since the required ID is already in the organization :) - Mike