ana ng ana ng - 9 months ago 32
SQL Question

My inner join resulted in the repitition of many rows

I am a novice in SQL so please bear with me.

I wrote the following query:

SELECT
CAN.Cycle
, CAN.FECCandID
, CAN.CID
, CAN.FirstLastP
, CAN.Party
, CAN.DistIDRunFor
, CAN.DistIDCurr
, CAN.CurrCand
, CAN.CycleCand
, CAN.CRPICO
, CAN.RecipCode
, CAN.NoPacs
FROM Cands16 AS CAN
JOIN MercerRobert_Indivs AS MER
ON CAN.CID = MER.RecipID


My goal was to return every row from the Cands16 in which CID = RecipID. This was the result:

pic

While the MER table does have rows with multiple incidences of the same value for RecipID every incidence of CID in the Cands16 table is unique. I do not want these duplicate rows resulting from my query. So what should I do? I am using SQL Server 2016 Management Studio.

Answer Source

Seeing you do not use any columns from MER, it seems you just want to know whether there exists such id in MER. So the easiest would be to remove the join:

select
   CAN.Cycle
   , CAN.FECCandID
   , CAN.CID
   , CAN.FirstLastP
   , CAN.Party
   , CAN.DistIDRunFor
   , CAN.DistIDCurr
   , CAN.CurrCand
   , CAN.CycleCand
   , CAN.CRPICO
   , CAN.RecipCode
   , CAN.NoPacs 
from Cands16 CAN
where exists (
    select * 
    from MercerRobert_Indivs MER 
    where CAN.CID = MER.RecipID
  ) ;