Geek Answers Handbook

SQL JOIN gives double results

My database and SQL:

http://sqlfiddle.com/#!9/ebddb/1/0

Problem:

It returns duplicates with incorrect data in name-column when in notchtype-table

less than 7 entries

My question is:

Why does he return duplicates and how to prevent them?

Expected Result:

This script shows the expected result: http://sqlfiddle.com/#!9/22660/1

As a result of this, the only thing added more than in my actual database and SQL is 2 entries in notchtype-table

Therefore, columns id, notchidand numbermust be unique in the returned string.

. SQL MariaDB 10.1.9 -

:
  • , , notchtype 7 , , "".
  • null null.
  • size -column , LEFT JOIN
  • notches.notchdescr "" notchtype.notchtypeid name
  • notches.notchsize "" notchsize.notchsizeid size

:
  • INNER JOIN, ,
  • DISTINCT, name - ,
  • GROUP BY, name -column

/ Piyush Gupta​​strong >

, MySQL 5.7:

SELECT 
        notches.id,
        notches.notchid,
        notches.number,
        notches.xcoord,
        notches.ycoord,
        notches.mapid,
        notches.location,
        notches.date,
        notches.price,
        notches.invoiced,
        notchsize.size AS notchsize,
        notchtype.name AS notchdescr
FROM 
    notches 
LEFT JOIN
    notchtype ON
    notches.notchdescr = notchtype.notchtypeid
LEFT JOIN
    notchsize ON
    notches.notchsize = notchsize.notchsizeid
WHERE 
    notches.del = 0 
AND
    notches.projectid = '2016032411364363055'
GROUP BY notches.id, notches.notchid, notches.number
ORDER BY notches.number ASC

:

Result

!

LEFT JOIN VARCHAR= BIGINT . . .

+4
2

GROUP BY . ,

SELECT 
        notches.id,
        notches.notchid,
        notches.number,
        notches.xcoord,
        notches.ycoord,
        notches.mapid,
        notches.location,
        notches.descr,
        notches.date,
        notches.price,
        notches.invoiced,
        notchtype.name AS notchdescr,
        notchsize.size AS notchsize
FROM 
    notches 
LEFT JOIN
    notchtype ON
    notches.notchdescr = notchtype.notchtypeid
LEFT JOIN
    notchsize ON
    notches.notchsize = notchsize.notchsizeid
WHERE 
    notches.del = 0 
AND
    notches.projectid = '2016032411364363055'
    GROUP BY notches.id,
        notches.notchid,
        notches.number
ORDER BY notches.number ASC;

:

.. , , , SQLFiddle, notchtype.name. NOTchdescr SQLFiddle, notchtype. , . , .

( MySQL Workbench) enter image description here

1. . , . bigint varchar, . bigint varchar notchsizeid notchsize notchtypeid notchtype. , . .

+4

, . .

 SELECT 
        notches.id,
        notches.notchid,
        notches.number,
        notches.xcoord,
        notches.ycoord,
        notches.mapid,
        notches.location,
        notches.descr,
        notches.date,
        notches.price,
        notches.invoiced,
        notchtype.name AS notchdescr,
        notchsize.size AS notchsize
FROM 
    notches 
LEFT JOIN
    notchtype ON
    notches.notchdescr = notchtype.notchtypeid
LEFT JOIN
    notchsize ON
    notches.notchsize = notchsize.notchsizeid
WHERE 
    notches.del = 0 
AND
    notches.projectid = '2016032411364363055'
    group by id
ORDER BY notches.number ASC
0

More articles:


All Articles