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

ahmed salah 3,206 Reputation points

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)  
insert into #Parts(Partid,FeatureId,FeatureValue)  

expected result


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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,569 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,454 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 16,851 Reputation points Microsoft Vendor

    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)  


    Best regards,

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Jingyang Li 5,806 Reputation points
     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