title: How to check an id being tied to its related table for only one value
body: <p>I have two tables in which I need to check whether UCCID is tied to only one BUYID as part of testing. UCCID should be unique for my table. But BUyid can have multiple UCCID. But UCCID should be tied to unique (only one) BUYID.</p>
  
  <pre><code>CREATE TABLE [dbo].[EquipmentBase](
             [EDAEquipmentId] [uniqueidentifier] NOT NULL,
             [BuyID] [nvarchar](100) NULL,
             [EqtAE] [nvarchar](100) NULL,
             [EqtSn] [nvarchar](100) NULL,
             [EqtSz] [nvarchar](100) NULL,
             [EqtUccYr] [nvarchar](100) NULL,
             [EqtUnit] [nvarchar](100) NULL,
             [EqtValue] [nvarchar](100) NULL,
             [UccID] [NVARCHAR](100) NULL,
             CONSTRAINT [PK__EquipmentBase] PRIMARY KEY CLUSTERED
      (
             [_EDAEquipmentId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
      ) ON [PRIMARY]
  
      GO
  
  
      CREATE TABLE [dbo].[activityBase](
             [activityId] [UNIQUEIDENTIFIER] NOT NULL,
             [UccID] [NVARCHAR](100) NULL,
             [ASCity] [NVARCHAR](100) NULL,
             [ASClass] [NVARCHAR](100) NULL,
             [ASComp] [NVARCHAR](100) NULL,
             [ASID] [NVARCHAR](100) NULL,
             [ASState] [NVARCHAR](100) NULL,
             [BuyADR1] [NVARCHAR](100) NULL,
             [BuyADR2] [NVARCHAR](100) NULL,
             [BuyID] [NVARCHAR](100) NULL,
             [UccCom] [NVARCHAR](100) NULL,
             [UccDate] [DATETIME] NULL,
             [UccFips2] [NVARCHAR](100) NULL,
             [UccStatus] [NVARCHAR](100) NULL,
             [UccTransID] [NVARCHAR](100) NULL,
             CONSTRAINT [PK__activityBase] PRIMARY KEY CLUSTERED
      (
             [activityId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
      ) ON [PRIMARY]
  
      GO
  </code></pre>
  
  <p>I tried the below query but I am not getting the result for what I am looking into? Can anyone help pls? I expect the below query to return 0 so that UCCID is tied to single BUYID.</p>
  
  <pre><code>  SELECT COUNT(*) FROM dbo.activityBase ab
          JOIN (SELECT eb.BuyID FROM dbo.EquipmentBase eb
          WHERE eb.UccID IS NOT NULL
          GROUP BY eb.BuyID
          HAVING COUNT(eb.BuyID)=1)c
          ON c.BuyID = ab.BuyID
  </code></pre>