Join in three tables MySQL

[cms9651]

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;

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.

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

[r937]

allow me to help you debug your own code

run this query --

SELECT * FROM tbl_l WHERE source = 'AD'

after you understand that, run this one --

SELECT * FROM tbl_n WHERE source = 'AD'

finally, after you understand that, run this one --

SELECT * FROM tbl_ip WHERE countrycode2 = 'AD'

[cms9651]

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 ...

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

[guido2004]

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 ):

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.

[cms9651]

Excellent guido2004. thanks a lot!
Small correction of details in the syntax GROU BY

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!