CODEWITHSUNDEEP
sql
raky
raky

Reputation: 31

Avoid Duplicates on SQL JOIN

I have two tables as below

Table A
ID   ID_Name    Amount
------------------------
1    ABC        200
2    XYZ        300

Table B
ID    Role    Name 
------------------
1     Plumber   John
1     Estimator  Redmond

I want to join both tables and I need the result as below


ID ID_Name  Plumber Estimator   Amount
--------------------------------------
1   ABC     John      Redmond   200

SQL I am using to JOIN 

Select A.ID, A.ID_Name

CASE WHEN B.Role='Plumber' THEN B.Name END AS Plumber,
CASE WHEN B.Role='Estimator' THEN B.Name END AS Estimator,
A.Amount
FROM A A
INNER JOIN B B ON A.ID=B.ID

```
How I can achieve this, when am trying to join I am getting two lines for the same ID and if do group by amount is 400

Upvotes: 2

Views: 73

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You should join the tableb two time one for Plumber and one for estimator

select a.ID,  a.ID_Name, b1.name plumber, b2.name estimator, a.amount
from tableA a
inner join tableb b1 on a.id = b1.id and b1.role ='Plumber'
inner join tableb b2 on a.id = b2.id and b2.role ='Estimator'

if you have duplicated row the you should use distinct 

select distinct  a.ID,  a.ID_Name, b1.name plumber, b2.name estimator, a.amount
from tableA a
inner join tableb b1 on a.id = b1.id and b1.role ='Plumber'
inner join tableb b2 on a.id = b2.id and b2.role ='Estimator'

Upvotes: 5

SaravananArumugam
SaravananArumugam

Reputation: 3720

While @scaisEdge will work for the given scenario, I would say you have to reconsider the schema of Table B. The result you are expecting is dynamic. With the query given above for now you are going doing roles in a line. What will happen if another new role gets introduced?

If Plumber and Estimator are mandated entities for a given Id, instead of having just TableB.Role may be redesign like TableB.Plumber, TableB.Estimator.

Upvotes: 0

Related Questions