CODEWITHSUNDEEP
sql
Björn C
Björn C

Reputation: 4008

SQL Why is my SELECT selecting duplicate rows?

This is a sub query:

SELECT t1.element_mark, t1.element_length, t1.element_width, t1.element_height, t1.product, t1.mass FROM IMP_ELEMENT as t1 WHERE TRIM(t1.project) = '99999';

This is the result: enter image description here

This is my second sub query:

SELECT t2.element_id, t2.building, t2.floor_id, t2.deleted, t2.ELEMENT_MARK
FROM IMP_MODEL_GEOMETRY as t2
WHERE TRIM(t2.project) = '99999' AND TRIM(t2.building) = '1' AND TRIM(CAST(t2.floor_id AS VARCHAR(MAX))) = '1' AND t2.deleted = 0
ORDER BY t2.ELEMENT_MARK;

This is the result: enter image description here

Now i'd like to combine them:

SELECT t1.element_mark, t1.element_length, t1.element_width, t1.element_height, t1.product, t1.mass, t2.element_id, t2.building, t2.floor_id, t2.deleted
FROM IMP_ELEMENT as t1
LEFT JOIN IMP_MODEL_GEOMETRY as t2 ON t1.element_mark = t2.element_mark
WHERE TRIM(t2.project) = '99999' AND TRIM(t2.building) = '1' AND TRIM(CAST(t2.floor_id AS VARCHAR(MAX))) = '1' AND t2.deleted = 0
ORDER BY t2.element_id;

And this is the result: enter image description here

So what do i want?
I want only one result per "element_id".
I first want to SELECT"element_id" and then i want to find a match in the second table. To find a match i will use "element_mark". What is wrong in my query? why do i get multiple element_id?

Upvotes: 0

Views: 58

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you want to keep all elements and get matching information -- if any -- from the second table, then you want the LEFT JOIN. However, all conditions on the second table need to be in the ON clause:

SELECT t1.element_mark, t1.element_length, t1.element_width, t1.element_height, t1.product, t1.mass,
       t2.element_id, t2.building, t2.floor_id, t2.deleted
FROM IMP_ELEMENT t1 LEFT JOIN 
     IMP_MODEL_GEOMETRY t2 
     ON t1.element_mark = t2.element_mark AND
        TRIM(t2.project) = '99999' AND
        TRIM(t2.building) = '1' AND 
        TRIM(CAST(t2.floor_id AS VARCHAR(MAX))) = '1' AND
        t2.deleted = 0
ORDER BY t2.element_id;

There is no WHERE clause because all the filters are on the second table.

Upvotes: 1

mck
mck

Reputation: 42352

As suggested by one of the comments, you probably have missed the WHERE clause in the first table. Try this query instead:

SELECT
    t1.element_mark, t1.element_length, t1.element_width, t1.element_height, t1.product, t1.mass, t2.element_id, t2.building, t2.floor_id, t2.deleted
FROM
    IMP_ELEMENT as t1
LEFT JOIN
    IMP_MODEL_GEOMETRY as t2
ON
    t1.element_mark = t2.element_mark
WHERE
    TRIM(t2.project) = '99999' AND
    TRIM(t2.building) = '1' AND
    TRIM(CAST(t2.floor_id AS VARCHAR(MAX))) = '1' AND
    t2.deleted = 0 AND
    TRIM(t1.project) = '99999'
ORDER BY t2.element_id;

Upvotes: 1

Peter Georgiev
Peter Georgiev

Reputation: 21

When joining two tables, in your case with a left join, if there are multiple matches on the join condition, multiple rows will be in the result set. You are joining based on element_mark- so for the first record of the first query- 3 rows will be returned in your result. What I would suggest is put the second table at the left part of the query or do a RIGHT JOIN. This way, because element_mark looks unique in the first table, you will get one record per element_id.

Upvotes: 1

Related Questions