How to replace where exists with inner join ?

ahmed salah 3,136 Reputation points
2021-09-13T10:36:19.893+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,751 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 94,416 Reputation points
    2021-09-13T10:46:43.5+00:00

    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 
    

  2. MelissaMa-MSFT 24,136 Reputation points
    2021-09-14T01:52:55.337+00:00

    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.

    0 comments No comments