How to select parts that have messages based on feature id and feature value ?

Question

I work on sql server 2019 i need to get message value for parts based on feature id and feature value

so if part id have same featureid and feature value of message then i will select message related to feature

create table #message  
(  
MessageId int,  
MessageValue varchar(200),  
FeatureId int,  
FeatureValue int  
)  
--drop table #Parts  
create table #Parts  
(  
PartId int,  
FeatureId int,  
FeatureValue int  
)  
  
insert into #message(MessageId,MessageValue,FeatureId,FeatureValue)  
values  
(1,'30v90v',9012,30),  
(1,'30v90v',9015,90),  
(2,'50v75v80v',9013,50),  
(2,'50v75v80v',9017,75),  
(2,'50v75v80v',9018,80),  
(3,'20v25v',9013,20),  
(3,'20v25v',9017,25),  
(4,'301v890v',9013,890),  
(4,'301v890v',9017,301)  
  
insert into #Parts(Partid,FeatureId,FeatureValue)  
values  
(127891,9012,30),  
(127891,9015,90),  
(904123,9013,50),  
(904123,9017,75),  
(904123,9018,80),  
(32901,9013,20),  
(32901,9017,99),  
(890215,9013,890)  

expected result

so how to write select statment to display part id and message value matched based on featureid and feature value

Answer 1

Hi @ahmed salah
Try this query:

SELECT MAX(PartId)AS PartId,MessageValue  
FROM #message M LEFT JOIN #Parts P ON M.FeatureId=P.FeatureId AND M.FeatureValue=P.FeatureValue  
GROUP BY MessageId,MessageValue  
HAVING COUNT(P.FeatureId)=COUNT(M.FeatureId)  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly 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 2

 Select p.PartId,Max(m.MessageValue) MessageValue  
 From    #Parts p   
 JOIN #message m  ON p.FeatureId=m.FeatureId and m.FeatureValue=p.FeatureValue  
 Group by p.partid  
 Having(COUNT(*)>1)