SQL JOIN gives double results

Question

My database and SQL:

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:

SOLVED!

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

Piyush Gupta · Accepted 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)

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.

SQL JOIN gives double results

Answers (2)

Related Questions