MySQL: INNER JOIN query in an EITHER-OR situation-CodePudding

I need to find all Users that belong to a certain Country (ID = 4321). But a User's Location can have...

EITHER a City
OR a Cityaddition, as you can see in...

The database schema:

User -< Locations >- City         >- District >- Region >- Country
                  >- Cityaddition >- District >- Region >- Country


Legend: (read from left to right)

-< : "...has many..."
>- : "...has one..."

How do I have to modify my query below in order to make this EITHER-OR condition work in 1 single query?

(Currently, this query only selects Users that have BOTH a City AND a Cityaddition, which is never the case in reality.)

SELECT users.* FROM users

INNER JOIN locations ON locations.user_id = users.id

INNER JOIN cities    ON cities.id = locations.city_id
INNER JOIN districts ON districts.id = cities.district_id
INNER JOIN regions   ON regions.id = districts.region_id

INNER JOIN cityadditions           ON cityadditions.id = locations.cityaddition_id
INNER JOIN districts
           districts_cityadditions ON districts_cityadditions.id = cityadditions.district_id
INNER JOIN regions
           regions_districts       ON regions_districts.id = districts_cityadditions.region_id

WHERE (regions.country_id = 4321)

CodePudding user response：

As a single query, you can do based on a parallel hierarchy and doing a logical NOT NULL on either side. Two separate via would be better as Barmar indicates due to the explicit OR.

Also, use aliases to shorten and make more readable and you can remove all those ticks. You typically only need those when dealing with column names that are also reserved words.

SELECT 
      u.* 
   FROM 
      users u
         JOIN locations l
            ON u.id = l.user_id
            LEFT JOIN cities c
               ON l.city_id = c.id
               JOIN districts d
                  ON c.district_id = d.id
                  INNER JOIN regions r
                     ON d.region_id = r.id
                    AND r.country_id = 4321
            LEFT JOIN cityadditions ca
               ON l.cityaddition_id =  ca.id
               JOIN districts d2
                  ON ca.district_id = da.id
                  JOIN regions r2
                     ON d2.region_id = r2.id
                    AND r2.country_id = 4321
   where
         r.id IS NOT NULL
      OR r2.id IS NOT NULL

CodePudding user response：

Using UNION, but minimising code repetition, and the number of redundant joins...

The planner is also able to use indexing to minimise query cost

SELECT DISTINCT -- all queries MAY need this, depending on the structure of your data
  `users`.*
FROM
  `regions`
INNER JOIN
  `districts`
    ON `districts`.`region_id` = `regions`.`id`
INNER JOIN
(
  SELECT
    `locations`.`id`,
    `cities`.`district_id`
  FROM
    `locations`
  INNER JOIN
    `cities`
        ON `cities`.`id` = `locations`.`city_id`

  UNION ALL

  SELECT
    `locations`.`id`,
    `cityadditions`.`district_id`
  FROM
    `locations`
  INNER JOIN
    `cityadditions`
        ON `cityadditions`.`id` = `locations`.`cityaddition_id`
)
  AS `locations`  
    ON `locations`.`district_id` = `district`.`id`
INNER JOIN
  `users`
    ON `users`.`location_id` = `locations`.`id`
WHERE
  `regions`.`country_id` = 4321