question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked MelissaMa-msft commented

How to replace where exists with inner join ?

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-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Why do you feel the need to replace exists with inner join? Do you need data from getrelatedkeyandvaluepackage?

If there is more than 1 match in getrelatedkeyandvaluepackage, the inner join will cause your code to return more rows than before.

1 Vote 1 ·

Hi @ahmedsalah-1628,

Could you please validate all the answers so far and provide any update?

Best regards,
Melissa

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ErlandSommarskog commented

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 


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That does not seem like good advice. This will return one row of there are multiple matches, but which row is undefined. Writing such queries are odd, to say the least.

Ahmed needs to clarify what he is looking for.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ahmedsalah-1628,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.