Sql query to skip the matched ones

Question

Hi,

--My query
SELECT WN.ANUMBER, WN.DEPTKEY, COUNT(DISTINCT WN.APPKEY) AS appkeycnt FROM DBO.VW_NUMBER WN
WHERE WN.ANUMBER IN
('GNW0060077','0456618','NGL0300008','235493', 'AD80009', 'PMA3345' )
AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS')
GROUP BY WN.DEPTKEY, WN.ANUMBER HAVING COUNT(DISTINCT WN.APPKEY) = 1

The above query will skip the ANUMBERS which does not exists in the table (example: ANUMBER AD80009 does not exists in the table) and also the ANUMBERS which are conflicting (example ANUMBERs NGL0300008, 235493 are conflicting. conflicting ANUMBER means “the same DEPTKEY and ANUMBER combination” are matched with other APPKEY with in the same Unit. )

Need help to update my above query to handle a scenario "I want to also skip the ANUMBER which are linked to more than one APPKEY"??

Below is the sample create and insert scripts:

--create script table 1  
CREATE TABLE [dbo].[VW_DATA](  
[ID] [tinyint] IDENTITY(1,1) NOT NULL,  
[APPKEY] int NOT NULL,  
[APPNAME] [varchar](20) NOT NULL,  
[DEPTKEY] int NOT NULL,  
[DEPTNAME] [varchar](20) NOT NULL,  
[UNITKEY] int NOT NULL,  
[UNITNAME] [varchar](20) NOT NULL,  
CONSTRAINT [PK_VW_DATA] PRIMARY KEY CLUSTERED  
(  
[ID] ASC  
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO  
  
  
--insert script table 1  
INSERT INTO [dbo].[VW_DATA]([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('1234','APP1','1','DEPT1','1','MAS')  
GO  
INSERT INTO [dbo].[VW_DATA]([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('1234','APP1','2','DEPT2','1','MAS')  
GO  
INSERT INTO [dbo].[VW_DATA]([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('5678','APP2','2','DEPT2','1','MAS')  
GO  
INSERT INTO [dbo].[VW_DATA]([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('9012','APP3','3','DEPT3','1','MAS')  
GO  
INSERT INTO [dbo].[VW_DATA]([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('9012','APP3','4','DEPT4','1','MAS')  
GO  
INSERT INTO [dbo].[VW_DATA]([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('3456','APP4','4','DEPT4','1','MAS')  
GO  
INSERT INTO [dbo].[VW_DATA]([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('7890','APP5','3','DEPT3','1','MAS')  
GO  
INSERT INTO [dbo].[VW_DATA]([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('7890','APP5','4','DEPT4','1','MAS')  
GO  
INSERT INTO [dbo].[VW_DATA] ([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('4334','APP6','6','DEPT6','1','MAS')  
GO  
  
  
--create script table 2  
CREATE TABLE [dbo].[VW_NUMBER](  
[ID] [tinyint] IDENTITY(1,1) NOT NULL,  
[APPKEY] int NOT NULL,  
[ANUMBER] [varchar](20) NOT NULL,  
[DEPTKEY] int NOT NULL,  
[UNITKEY] int NOT NULL,  
CONSTRAINT [PK_VW_NUMBER] PRIMARY KEY CLUSTERED  
(  
[ID] ASC  
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO  
  
--insert script table 2  
INSERT INTO [dbo].[VW_NUMBER]([APPKEY],[ANUMBER],[DEPTKEY],[UNITKEY]) VALUES ('1234','GNW0060077','1','1')  
GO  
INSERT INTO [dbo].[VW_NUMBER]([APPKEY],[ANUMBER],[DEPTKEY],[UNITKEY]) VALUES ('1234','0456618','2','1')  
GO  
INSERT INTO [dbo].[VW_NUMBER]([APPKEY],[ANUMBER],[DEPTKEY],[UNITKEY]) VALUES ('9012','NGL0300008','3','1')  
GO  
INSERT INTO [dbo].[VW_NUMBER]([APPKEY],[ANUMBER],[DEPTKEY],[UNITKEY]) VALUES ('9012','235493','4','1')  
GO  
INSERT INTO [dbo].[VW_NUMBER]([APPKEY],[ANUMBER],[DEPTKEY],[UNITKEY]) VALUES ('7890','NGL0300008','3','1')  
GO  
INSERT INTO [dbo].[VW_NUMBER]([APPKEY],[ANUMBER],[DEPTKEY],[UNITKEY]) VALUES ('7890','235493','4','1')  
GO  
INSERT INTO [dbo].[VW_NUMBER]([APPKEY],[ANUMBER],[DEPTKEY],[UNITKEY]) VALUES ('3456','PMA3345','6','1')  
GO  
INSERT INTO [dbo].[VW_NUMBER]([APPKEY],[ANUMBER],[DEPTKEY],[UNITKEY]) VALUES ('4334','PMA3345','4','1')  
GO  
  
  

Thank you.

Answer 1

Hi, @Vamshi

You only need to make some changes to your code. The appearance of the WN.DEPTKEY column is a problem that causes multiple records to have the same number. The core idea of lijingyang's writing is also the same. When you compare some results, you will be more It is easy to understand everyone's thinking.Please Try this way:

SELECT WN.ANUMBER,  COUNT(DISTINCT WN.APPKEY) AS appkeycnt   
FROM DBO.VW_NUMBER WN  
join DBO.VW_DATA  Ic on ic.APPKEY=WN.APPKEY  
 WHERE WN.ANUMBER IN  
 ('GNW0060077','0456618','NGL0300008','235493', 'AD80009', 'PMA3345' )  
 AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS')  
 GROUP BY  WN.ANUMBER HAVING COUNT(DISTINCT WN.APPKEY) = 1  

Bert Zhou

Answer 2

Hi @Vamshi

Then you can just group by field ANUMBER ,then use left join to get the field DEPTKEY

 with cte as(SELECT WN.ANUMBER,  COUNT(DISTINCT WN.APPKEY) AS appkeycnt FROM DBO.VW_NUMBER WN  
WHERE WN.ANUMBER IN  
('GNW0060077','0456618','NGL0300008','235493', 'AD80009', 'PMA3345' )  
AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS')  
GROUP BY  WN.ANUMBER HAVING COUNT(DISTINCT WN.APPKEY) = 1)  
select * from cte c left join DBO.VW_NUMBER o on o.ANUMBER = c.ANUMBER   

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

 ;with mycte as (  
 SELECT WN.ANUMBER, WN.DEPTKEY, WN.APPKEY  
 ,row_number() Over(partition by WN.DEPTKEY, WN.ANUMBER Order by WN.APPKEY) rn1   
    ,count(*) Over(partition by WN.ANUMBER  ) rn2   
	,count(*) Over(partition by WN.APPKEY,WN.ANUMBER  ) rn3  
 ,COUNT(WN.APPKEY) Over(partition by WN.DEPTKEY, WN.ANUMBER) AS appkeycnt   
 FROM DBO.VW_NUMBER WN WHERE WN.ANUMBER IN ('GNW0060077','0456618','NGL0300008','235493', 'AD80009', 'PMA3345' )  
 AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS')  
 )  
      
 Select ANUMBER, DEPTKEY,APPKEY, appkeycnt   
 from mycte  
 where rn1=1 and  rn2=1 and rn3=1   
 and appkeycnt=1  

Answer 4

;with mycte as (  
SELECT WN.ANUMBER, WN.DEPTKEY, WN.APPKEY  
,row_number() Over(partition by WN.DEPTKEY, WN.ANUMBER Order by WN.APPKEY) rn   
   
,COUNT(WN.APPKEY) Over(partition by WN.DEPTKEY, WN.ANUMBER) AS appkeycnt   
FROM DBO.VW_NUMBER WN WHERE WN.ANUMBER IN ('GNW0060077','0456618','NGL0300008','235493', 'AD80009', 'PMA3345' )  
AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS')  
)  
  
Select ANUMBER, DEPTKEY, appkeycnt  
from mycte  
where rn=1 and appkeycnt=1