Antonio Mailtraq Antonio Mailtraq - 2 months ago 6
MySQL Question

Records doubled on sql query left join

I have 2 tables and I am using join to get common records from those 2 tables.

I have used the following query but my problem is I am getting the records doubled.

The query is as follows, can you help me ?

Query #1 : ( correct output )

mysql> SELECT DISTINCT
xId1 AS xId1,
xArea AS xArea,
YEAR (
STR_TO_DATE(
`xDate`,
'%d/%m/%Y'
)
) AS xYear
FROM
doTable1 AA
LEFT JOIN doTable2 BB ON AA.xId1 = BB.xId2
WHERE
xArea IN ('XX1A')
AND YEAR (
STR_TO_DATE(
`xDate`,
'%d/%m/%Y'
)
) = 2016;

+-----------+-------+-------+
| xId | xArea | xYear |
+-----------+-------+-------+
| 91623545 | XX1A | 2016 |
| 89748942 | XX1A | 2016 |
| 115956872 | XX1A | 2016 |
| 103595808 | XX1A | 2016 |
| 103623873 | XX1A | 2016 |
| 103623892 | XX1A | 2016 |
| 103623872 | XX1A | 2016 |
| 103623894 | XX1A | 2016 |
| 112072738 | XX1A | 2016 |
| 112072751 | XX1A | 2016 |
| 117818773 | XX1A | 2016 |
| 117818753 | XX1A | 2016 |
| 117885105 | XX1A | 2016 |
| 117960040 | XX1A | 2016 |
| 117885111 | XX1A | 2016 |
+-----------+-------+-------+

15 rows in set


Query #2 : ( incorrect output because for 2016 year I have 18 records - doubled presence - and not 15 records as on Query #1):

mysql> SELECT
DISTINCT COUNT(*) AS xQQQ,
xArea AS xArea,
YEAR (
STR_TO_DATE(
xDate,
'%d/%m/%Y'
)
) AS xYear
FROM
doTable1 AA
LEFT JOIN doTable2 BB ON AA.xId1 = BB.xId2
WHERE xArea IN ('XX1A')
GROUP BY
xArea,
xYear;
+------+-------+-------+
| xQQQ | xArea | xYear |
+------+-------+-------+
| 4 | XX1A | 2014 |
| 49 | XX1A | 2015 |
| 18 | XX1A | 2016 |
+------+-------+-------+
3 rows in set

Answer

Modified your SQL. if you want distinct counts, have to use like this

COUNT(DISTINCT xId1 )

Please run and see.

SELECT 
        COUNT(DISTINCT xId1 ) AS xQQQ,
        xArea AS xArea,
        YEAR (
            STR_TO_DATE(
                `DATA PUBBLICAZIONE`,
                '%d/%m/%Y'
            )
        ) AS xYear
    FROM
        doTable1 AA
    LEFT JOIN doTable2 BB ON AA.xId1 = BB.xId2
    WHERE xArea IN ('XX1A')
    GROUP BY
        xArea,
        xYear;
Comments