SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Uruguay
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get table records joining with others 2 tables

    Hi,
    I have 3 tables

    Code:
    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. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Bearing the assumption that active is 0 or 1 this would do it:

    Code SQL:
    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
    The only code I hate more than my own is everyone else's.

  3. #3
    Non-Member vogo1988's Avatar
    Join Date
    Sep 2012
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its right ~~
    Quote Originally Posted by oddz View Post
    Bearing the assumption that active is 0 or 1 this would do it:

    Code SQL:
    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

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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):

    Code:
    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rcashell View Post
    INNER JOIN services AS s USING (id)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rcashell View Post
    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):

    Code:
    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;
    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.
    The only code I hate more than my own is everyone else's.

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Uruguay
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post

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

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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ratamaster View Post
    I just want a list of Countries when each one has at least one associated active Service.
    my response was for the poor soul who suggested using the USING option

    Code:
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Uruguay
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, thanks you all !


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •