Post reply

Subqueries to Inner Joins

  • September 22, 2010 at 3:57 am

    #238577

    Hi all, how would these selects be written as joins?

    Thanks, BLL

    --Checking for Services records for all services under each MTNet

    select * from services (nolock) where service in (select service from defineservice (nolock) where mtnets in (20))

    select * from services (nolock) where service in (select service from defineservice (nolock) where mtnets in (472))

    -- Checking for phonelist records for all services on original shortcode

    select * from phonelist where userid in (select userid from services (nolock) where service in (select service from defineservice (nolock) where mtnets in (20)))

    -- Updating defineservice to new MTNets

    update defineservice set mtnets=472 where mtnets=20

    --Updating Phonelist records to new Aggregator

    update phonelist set aggregator=472 aggregator=20 and userid in

    (select userid from services (nolock) where service in (select service from defineservice (nolock) where mtnets in (20)))

  • September 22, 2010 at 4:14 am

    #1225434

    Why do you want to write them as joins?

    Changing IN to inner join may change the meaning of the query (introduce extra rows)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 22, 2010 at 9:16 am

    #1225686

    blacklabellover2003 (9/22/2010)

    --Checking for Services records for all services under each MTNet

    select * from services (nolock) where service in (select service from defineservice (nolock) where mtnets in (20))

    Hi black,

    You want to convert them like this.

    SELECT

    *

    FROM

    services (nolock)

    INNER JOIN

    (

    SELECT

    service

    FROM

    defineservice (nolock)

    WHERE

    mtnets in (20)

    ) AS SubQuery ON

    services.service = SubQuery.service

    Gail: can you explain what you mean I am probably missing something here and don't want to be giving this fellow bad advice !

  • September 22, 2010 at 9:21 am

    #1225693

    Maxim Picard (9/22/2010)

    Gail: can you explain what you mean I am probably missing something here and don't want to be giving this fellow bad advice !

    Sure...

    http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

    You will I hope forgive me for not writing it all out again.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 22, 2010 at 9:35 am

    #1225700

    GilaMonster (9/22/2010)

    Sure...

    http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

    You will I hope forgive me for not writing it all out again.

    Thank you, I knew there was something

    Very nice article

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply