How to replace where exists with inner join ?

Question

I work on sql server 2012 i face issue ican't replace (where exists)
by inner join
so How to do it

SELECT pr.partid

        from 

                 parts.Nop_Part pr with(nolock) 
                inner join extractreports.dbo.RequiredPartsPL rp with(nolock) on rp.partid=pr.partid

                inner join Parts.Nop_PackageAttribute pa WITH(NOLOCK) on pa.packageid=pr.packageid 

                inner JOIN dbo.Core_DataDefinitionDetails dd WITH(NOLOCK) ON pa.[Key] = dd.ColumnNumber --and dd.acceptedvalueid=64


                where     
  exists(select 1 from extractreports.dbo.getrelatedkeyandvaluepackage g where g.Featureid=dd.acceptedvalueid and g.valueid=pa.value  )

                group by pr.partid 

so how to replace statement above by inner join instead of using where exists
statment

Answer 1

Try something like this:

inner join (select top(1) Featureid, valueid from extractreports.dbo.getrelatedkeyandvaluepackage) g on g.Featureid = dd.acceptedvalueid and g.valueid = pa.value 

Answer 2

Hi @ahmed salah ,

What is the purpose for replacing exists with inner join？

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data and the expected result of the sample after executing your query.

Actually EXISTS and INNER JOIN have their own uses. If you only would like to return some records from a set, use EXISTS. If you also would like to return the columns in the another table, use INNER JOIN. You only have to notice the situation of one-many.

I made a test from my side by creating your tables and inserting some sample data and it was working like below:

SELECT distinct pr.partid    
from   parts.Nop_Part pr with(nolock)   
inner join extractreports.dbo.RequiredPartsPL rp with(nolock) on rp.partid=pr.partid                      
inner join Parts.Nop_PackageAttribute pa WITH(NOLOCK) on pa.packageid=pr.packageid   
inner JOIN dbo.Core_DataDefinitionDetails dd WITH(NOLOCK) ON pa.[Key] = dd.ColumnNumber  
inner join extractreports.dbo.getrelatedkeyandvaluepackage g on g.Featureid=dd.acceptedvalueid and g.valueid=pa.value   

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.