What is up with this?!!! (for real this time)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: What is up with this?!!! (for real this time)

  1. #1
    mark Guest

    What is up with this?!!! (for real this time)


    I am trying to get a list of modules that do not have associated submodules.
    I don't think I'm doing anything wrong. Could the index table be screwed
    up or am I just stoopid or both?

    select * from modules where moduleid = 892 : returns 1 record (true)

    select * from submodules where moduleid = 892 " returns 0 records (true)

    select * from modules where moduleid not in (select moduleid from submodules)
    : returns 0 records... what the ?!

    select moduleid from modules where not exists (select moduleid from submodules)
    : returns 0 records...

    Whiskey Tango Foxtrot? ... Over.


    Mark

  2. #2
    Rune Bivrin Guest

    Re: What is up with this?!!! (for real this time)

    Answers inline.

    "mark" <mtaylor@globalsolar.com> wrote in news:3d499978$1@10.1.10.29:

    > select * from modules where moduleid not in (select moduleid from
    > submodules)
    >: returns 0 records... what the ?!


    Don't quite know about this one. Have you tried reversing the condition
    [where moduleid IN (select ...] to see if that returns all rows from
    modules? What is the data type of the columns in the two tables. Are they
    different?


    > select moduleid from modules where not exists (select moduleid from
    > submodules)
    >: returns 0 records...


    As written, it will return no rows from modules unless submodules is empty,
    since there's no correlation between query and sub-query

    I think that should read:

    select * from modules where not exists (select * from
    submodules where moduleid = modules.moduleid)

    Rune Bivrin


  3. #3
    mark Guest

    Re: What is up with this?!!! (for real this time)


    Rune, thanks for the research. I also came up with:

    select * from modules m
    left join submodules sm on sm.moduleid = m.moduleid
    where sm.moduleid is null

    901 records returned

    which yields the same result as your query:

    select * from modules where not exists (select * from
    submodules where moduleid = modules.moduleid)

    901 records returned

    Cool. Your query is probably better as long as moduleid is indexed on the
    submodule record. But I wonder if SQL Server still takes the same route.
    In theory it does.

    Mark





    Rune Bivrin <rune@bivrin.com> wrote:
    >Answers inline.
    >
    >"mark" <mtaylor@globalsolar.com> wrote in news:3d499978$1@10.1.10.29:
    >
    >> select * from modules where moduleid not in (select moduleid from
    >> submodules)
    >>: returns 0 records... what the ?!

    >
    >Don't quite know about this one. Have you tried reversing the condition


    >[where moduleid IN (select ...] to see if that returns all rows from
    >modules? What is the data type of the columns in the two tables. Are they


    >different?
    >
    >
    >> select moduleid from modules where not exists (select moduleid from
    >> submodules)
    >>: returns 0 records...

    >
    >As written, it will return no rows from modules unless submodules is empty,


    >since there's no correlation between query and sub-query
    >
    >I think that should read:
    >
    >select * from modules where not exists (select * from
    > submodules where moduleid = modules.moduleid)
    >
    >Rune Bivrin
    >



  4. #4
    mark Guest

    Re: What is up with this?!!! (for real this time)


    Theory about how SQL Server optimizes for the most efficient retrieval aside,
    your query uses ~ 50% less resources than my query. I ran both queries in
    a batch and displayed the execution plans. Interesting.

    Mark



    Rune Bivrin <rune@bivrin.com> wrote:
    >Answers inline.
    >
    >"mark" <mtaylor@globalsolar.com> wrote in news:3d499978$1@10.1.10.29:
    >
    >> select * from modules where moduleid not in (select moduleid from
    >> submodules)
    >>: returns 0 records... what the ?!

    >
    >Don't quite know about this one. Have you tried reversing the condition


    >[where moduleid IN (select ...] to see if that returns all rows from
    >modules? What is the data type of the columns in the two tables. Are they


    >different?
    >
    >
    >> select moduleid from modules where not exists (select moduleid from
    >> submodules)
    >>: returns 0 records...

    >
    >As written, it will return no rows from modules unless submodules is empty,


    >since there's no correlation between query and sub-query
    >
    >I think that should read:
    >
    >select * from modules where not exists (select * from
    > submodules where moduleid = modules.moduleid)
    >
    >Rune Bivrin
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center