CODEWITHSUNDEEP
sqlsql-serverselectjoin
Arif YILMAZ
Arif YILMAZ

Reputation: 5866

sql select returns duplicate rows because of joins

I am doing a join in select statement and it returns duplicate professional id because of the join.

Select P.ProfessionalID,  P.ProfessionalName , S.SpecialtyName
    from Professionals P, ProfessionalSpecialtyRelation PR,  Specialties S 
where
    ( P.ProfessionalName Like '%arif%' OR P.ProfessionalSurname Like '%%'
    )
    And P.ProfessionalID = PR.ProfessionalID
    AND P.ProfessionalID = POR.ProfessionalID
    AND PR.SpecialtyID = S.SpecialtyID

If Prof has two Specialities, it returns that Prof twice. How can I avoid that? what is the best way to do it?

Upvotes: 0

Views: 13898

Answers (3)

Taryn
Taryn

Reputation: 247650

If you only want to return one speciality then you can use an aggregate (max/min) function:

Select P.ProfessionalID,  
  P.ProfessionalName, 
  max(S.SpecialtyName) SpecialtyName
from Professionals P
inner join ProfessionalSpecialtyRelation PR
  on P.ProfessionalID = PR.ProfessionalID
  -- and P.ProfessionalID = POR.ProfessionalID  You are not joining to a table with POR alias
inner join Specialties S 
  on PR.SpecialtyID = S.SpecialtyID
where P.ProfessionalName Like '%arif%' 
  OR P.ProfessionalSurname Like '%%'
group by P.ProfessionalID, P.ProfessionalName;

Or since you are using SQL Server you can also use the row_number() function to return only one row for each professional:

select ProfessionalID,
  ProfessionalName,
  SpecialtyName
from 
(
  Select P.ProfessionalID,  
    P.ProfessionalName, 
    S.SpecialtyName,
    row_number() over(partition by P.ProfessionalID order by S.SpecialtyName) rn
  from Professionals P
  inner join ProfessionalSpecialtyRelation PR
    on P.ProfessionalID = PR.ProfessionalID
    -- and P.ProfessionalID = POR.ProfessionalID  You are not joining to a table with POR alias
  inner join Specialties S 
    on PR.SpecialtyID = S.SpecialtyID
  where P.ProfessionalName Like '%arif%' 
    OR P.ProfessionalSurname Like '%%'
) d
where rn = 1;

Note: I changed the query to use ANSI JOIN syntax (INNER JOIN) instead of the comma separated list with the joins in the WHERE clause.

Upvotes: 1

Karan Gandhi
Karan Gandhi

Reputation: 1494

remove S from join, and use sub-query to get result with comma. Use with clause or other ideas. I hope it well help you. Best of luck.

Upvotes: 1

Cyril Gandon
Cyril Gandon

Reputation: 17048

You don't explain what is the speciality you want for your professor having two specialities.
You need to group by professor, and apply an aggregate function on the specialities. Depending on the aggregate function, you will get different results:

SELECT P.ProfessionalID
     , P.ProfessionalName 
     , MAX(S.SpecialtyName)
FROM Professionals P
    INNER JOIN ProfessionalSpecialtyRelation PR
        ON P.ProfessionalID = PR.ProfessionalID
            INNER JOIN Specialties S 
                ON PR.SpecialtyID = S.SpecialtyID
WHERE P.ProfessionalName Like '%arif%' 
OR P.ProfessionalSurname Like '%%'
GROUP BY P.ProfessionalID,  P.ProfessionalName

With the MAX function, you will get the maxium of the specialty, in term of string.

Note that you should write your joins explicitly with JOIN clause, instead of writing it implicitly.

Upvotes: 2

Related Questions