CODEWITHSUNDEEP
sqlsql-server
user3242861
user3242861

Reputation: 1929

Getting duplicate records

In my application I have Materials and Handlings, each material can have several handlings.

So, I have a table material, handlings and handlingMaterial.

In my edit material view I want to list all existing handlings and check the handlings added to the specific material and my problem is this:

For example: I have the material:

ID | NAME
----------
1  | xpto

I have these handlings:

ID | Name
-----------------
1  | Temperature
2  | Painting
3  | Anti-humidity

And the HandlingMaterial that makes the relation between materials and handlings:

ID | ID_HANDLING | ID_MATERIAL
------------------------------
1  | 1           | 1
2  | 3           | 1

In this example we can say that material xpto have 2 handlings added.

In my query I want to return this:

ID | Name          | HANDLING
------------------------------
1  | Temperature   | 1
2  | Painting      | 0
3  | Anti-humidity | 1

But the result is this:

ID | Name          | HANDLING
------------------------------
1  | Temperature   | 0
1  | Temperature   | 1
2  | Painting      | 0
3  | Anti-humidity | 0
3  | Anti-humidity | 1


select distinct [u_handling_alb].*, 
    (CASE WHEN u_handlingMaterials_alb.u_material_id = 1 THEN 1 ELSE 0 END) AS handling 
    from [u_handling_alb] 
    left join [u_handlingMaterials_alb] on [u_handling_alb].[id ] = [u_handlingMaterials_alb].[u_handling_id] 
    where [u_handling_alb].[u_active] = 1

Duplicates the results where handling is 1, puts on value with 0 and another with 1. I don't understand why... the error could be in the case.

What is my problem?

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If I understand correctly, you want to move the condition to the on clause:

select h.*, 
       (case hm.u_material_id is not null when 1 then 1 else 0 end) as handling
from u_handling_alb h left join
     u_handlingMaterials_alb hm
     on h.id = hm.u_handling_id and
        hm.u_material_id = 1
where h.u_active = 1

Upvotes: 1

Related Questions