Ramon Bakker Ramon Bakker - 5 months ago 18
SQL Question

SQL JOIN gives double results

My database and SQL:

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


Problem:

It's returning duplicates, with the wrong data in the
name
-column, when there are less than 7 records in the
notchtype
-table



My Question:

Why does it return duplicates and how to prevent it?



Expected result:

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

In this result the only thing more added than in my actual database and SQL are 2 records in the
notchtype
-table


So the
id
,
notchid
and
number
columns should be unique in the returned rows.

The screenshot in the answer of Piyush Gupta is showing the right expected result. The same query on SQL fiddle and locally on MariaDB version 10.1.9 are returning something different



Notes:


  • I found out that when there at least 7 records in the
    notchtype
    table, there are suddenly no duplicates anymore and the problem is 'solved'.

  • The
    null
    values should indeed be
    null
    .

  • The
    size
    -column is actually returning the right values, although the
    LEFT JOIN
    is more or less the same

  • The ID's in
    notches.notchdescr
    'connects' with the ID's in
    notchtype.notchtypeid
    column and is returned as the
    name
    column in the fiddle

  • The ID's in
    notches.notchsize
    'connects' with the ID's in
    notchsize.notchsizeid
    column and is returned as the
    size
    column in the fiddle





Not working:


  • INNER JOIN
    , don't know why

  • DISTINCT
    , because the
    name
    -columns have different values, so there not exact duplicates

  • GROUP BY
    , because it returns all the same values in the
    name
    -column





Update on answer/comments from Piyush Gupta

Query executed on 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:

Result



SOLVED!

LEFT JOIN
on
VARCHAR
=
BIGINT
field causes the strange returned values. See answer and comments of Piyush Gupta

Answer

You missed the GROUP BY in your query for Aggregate the data. so your query will be,

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;

Output: ONLINE DEMO HERE

NOTE: I Imported your data structure locally and I'm getting same output which is your expectation but In SQLFiddle, notchtype.name AS notchdescr column is not executing in SQLFiddle that is showing only name column of notchtype table. So you can use above query and check locally in your database. I hope you will get require output.

Screenshot(Using MySQL Workbench) enter image description here

Update 1: It was strange error. I reviewed database structure and found solution that was data type issue only. You were joining bigint and varchar data type so you need to correct data type. Here I'm changing data type bigint to varchar for notchsizeid in notchsize table and notchtypeid in notchtype table. Finally your Expected output is coming. You can SEE OUTPUT HERE.