Get table records joining with others 2 tables

Databases
1

Hi,
I have 3 tables

CREATE TABLE `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
) 

CREATE TABLE `regions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
)

CREATE TABLE `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `region_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
)

So a Country has many Regions (1-n) and a Service has one Region(1-1).

I would like to get all Countries only if they have active Services (services.active = 1)

Do you know how build this query ?

Thanks in advence.

ps: I could do this but I think is very ugly:
SELECT DISTINCT countries.id, countries.name FROM countries, regions, services
where countries.id = regions.country_id
and regions.id = services.region_id

2

Bearing the assumption that active is 0 or 1 this would do it:


SELECT
       c.id
      ,c.name
  FROM
      countries c
 INNER
  JOIN
      regions r
    ON
      c.id = r.country_id
 INNER
  JOIN
      services s
    ON
      r.id = s.region_id
 GROUP
    BY
      c.id
HAVING
      MAX(s.active) = 1
3

its right ~~

4

There is no real need to have the HAVING clause, I would filter using in the WHERE clause (as this could potentially use indexes on the table):


SELECT DISTINCT c.id, c.name
FROM countries AS c
INNER JOIN regions AS r ON (c.id = r.country_id)
INNER JOIN services AS s USING (id)
WHERE s.active = 1;
5

no, no, no, no, no

my advice is ~never~ use the evil USING option for your joins

are you joining services.id to country.id? how is it supposed to know which ids you want to join?

if you don’t join on the right columns, you’re gonna have a bad time

you will be surprised at what mysql will actually do in this case… go ahead, test it

:slight_smile:

6

I was thinking it would be nice to have all service data available just in case some other aggregate calculation is needed that might include inactive services. For example using my method it would be easy to count the number of active and inactive services per country.

7

I just want a list of Countries when each one has at least one associated active Service.

8

my response was for the poor soul who suggested using the USING option :slight_smile:

SELECT id
     , name 
  FROM countries
 WHERE EXISTS
       ( SELECT NULL
           FROM regions
         INNER
           JOIN services
             ON services.region_id = regions.id
            AND services.active = 1
          WHERE regions.country_id = countries.id )
9

OK, thanks you all !