SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Join in three tables MySQL

    Hi all, hope in your help.

    I have three tables in MySQL.

    1) tbl_L with fields ID and source;
    2) tbl_N with fields ID, source and flags;
    3) tbl_ip with fields ID and COUNTRY_CODE.

    I need count rows in tbl_L grouped by source and associated for single source the field flags (of the tbl_N) and the field COUNTRY_CODE (of the tbl_ip) and I tried this join query;
    Code:
    mysql> SELECT
    	a.source,
    	CA.source,
    	CB.COUNTRY_CODE2,
            CB.FLAGS,
    	COUNT(*)
    FROM
    	tbl_L A
    JOIN tbl_N CA ON a.source = CA.source
    JOIN tbl_ip CB ON UCASE(CA.source) = CB.COUNTRY_CODE2
    WHERE
    	a.source NOT IN ('1X', '2F', '3T')
    GROUP BY
    	a.source
    LIMIT 5;
    +---------+---------+---------------+--------+----------+
    | source  | source1 | COUNTRY_CODE2 | FLAGS  | COUNT(*) |
    +---------+---------+---------------+--------+----------+
    | AD      | AD      | AD            | AD.PNG |       10 |
    | AL      | AL      | AL            | AL.PNG |       46 |
    | AR      | AR      | AR            | AR.PNG |     6435 |
    | AT      | AT      | AT            | AT.PNG |     6528 |
    | AU      | AU      | AU            | AU.PNG |     2532 |
    +---------+---------+---------------+--------+----------+
    5 rows in set
    This output is incorrect because if I tried this simple query count I have the exact numbers, can you help me?
    thank you.
    Code:
    mysql> SELECT
    	source,
    	COUNT(*)
    FROM
    	tbl_L
    WHERE
    	source NOT IN ('1X', '2F', '3T')
    GROUP BY
    	source
    LIMIT 5;
    +---------+----------+
    | source  | COUNT(*) |
    +---------+----------+
    | AD      |        1 |
    | AL      |        1 |
    | AR      |       11 |
    | AT      |        4 |
    | AU      |        1 |
    +---------+----------+
    5 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    allow me to help you debug your own code

    run this query --
    Code:
    SELECT * FROM tbl_l WHERE source = 'AD'
    after you understand that, run this one --
    Code:
    SELECT * FROM tbl_n WHERE source = 'AD'
    finally, after you understand that, run this one --
    Code:
    SELECT * FROM tbl_ip WHERE countrycode2 = 'AD'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for help.

    this is the output in debug.

    I understand the count is performed on the table tbl_ip and not on the table tbl_L ...
    Code:
    mysql> SELECT ID, SOURCE FROM tbl_L WHERE SOURCE = 'AD';
    
    SELECT ID, SOURCE FROM tbl_N WHERE SOURCE = 'AD';
    
    SELECT * FROM tbl_ip WHERE COUNTRY_CODE2 = 'AD';
    
    +------+---------+
    | ID   | SOURCE  |
    +------+---------+
    | 4653 | AD      |
    +------+---------+
    1 row in set
    
    +----+---------+
    | ID | SOURCE  |
    +----+---------+
    | 11 | AD      |
    +----+---------+
    1 row in set
    
    +--------+------------+------------+---------------+---------------+--------------+--------+
    | ID     | IP_FROM    | IP_TO      | COUNTRY_CODE2 | COUNTRY_CODE3 | COUNTRY_NAME | FLAGS  |
    +--------+------------+------------+---------------+---------------+--------------+--------+
    |  21235 | 1389296384 | 1389296447 | AD            | AND           | ANDORRA      | AD.PNG |
    |  24350 | 1432264704 | 1432272895 | AD            | AND           | ANDORRA      | AD.PNG |
    |  28238 | 1538998272 | 1539006463 | AD            | AND           | ANDORRA      | AD.PNG |
    |  34762 | 1836015616 | 1836023807 | AD            | AND           | ANDORRA      | AD.PNG |
    |  52571 | 3244845568 | 3244845823 | AD            | AND           | ANDORRA      | AD.PNG |
    |  61002 | 3262479282 | 3262479282 | AD            | AND           | ANDORRA      | AD.PNG |
    |  66903 | 3265150976 | 3265159167 | AD            | AND           | ANDORRA      | AD.PNG |
    |  76249 | 3278943684 | 3278943684 | AD            | AND           | ANDORRA      | AD.PNG |
    |  76284 | 3278943735 | 3278943735 | AD            | AND           | ANDORRA      | AD.PNG |
    | 107563 | 3637071888 | 3637071903 | AD            | AND           | ANDORRA      | AD.PNG |
    +--------+------------+------------+---------------+---------------+--------------+--------+
    10 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cms9651 View Post
    I understand the count is performed on the table tbl_ip and not on the table tbl_L ...
    No. The count is performed on the resulting table from the joins. With source 'AD' you have 1 x 1 x 10 = 10 rows, so the count returns 10.
    But if there would've been 2 'AD' rows in the second table, then the count would have returned 1 x 2 x 10 = 20.

    If you need to count the number of rows with source = 'AD' in the first table, then you'll have to use a subquery. Something like (it still won't give you what you want, but it resolves the count problem ):
    Code:
    SELECT
        a.source
      , CA.source
      , CB.COUNTRY_CODE2
      , CB.FLAGS
      , a.total
    FROM
       (SELECT 
            source
          , COUNT(*) AS total
        FROM tbl_L A
        GROUP BY source
       ) AS a
    INNER JOIN tbl_N CA 
    ON a.source = CA.source
    INNER JOIN tbl_ip CB 
    ON UCASE(CA.source) = CB.COUNTRY_CODE2
    WHERE a.source NOT IN ('1X', '2F', '3T')
    LIMIT 5
    By the way, I don't understand why you need the the second table in that join? You don't select anything from it, and you can link the first and third table directly.

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Excellent guido2004. thanks a lot!
    Small correction of details in the syntax GROU BY
    Code:
    SELECT
        a.source
      , CA.source
      , CA.source_full_name
      , CB.COUNTRY_CODE2
      , CB.FLAGS
      , a.total
    FROM
       (SELECT 
            source
          , COUNT(*) AS total
        FROM tbl_L A
        GROUP BY source
       ) AS a
    INNER JOIN tbl_N CA 
    ON a.source = CA.source
    INNER JOIN tbl_ip CB 
    ON UCASE(CA.source) = CB.COUNTRY_CODE2
    WHERE a.source NOT IN ('1X', '2F', '3T')
    GROUP BY
    	a.source
    ORDER BY
    	total DESC;
    
    Affected rows: 0
    Time: 1.969ms
    By the way, I don't understand why you need the the second table in that join? You don't select anything from it, and you can link the first and third table directly.
    Because in the table tbl_N I have full name of source, e.g.: source = US, full name = United States of America

    Good bye, thank you!
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •