none
What is the difference between these two queries? RRS feed

  • Question

  • These queries don't return the same results.  Can someone explain why?

    SELECT LuminiareRegionStaticMapID, RegionStaticMapID, LuminaireID, XPosition, YPosition FROM dbo.LuminairesRegionStaticMap
    where LuminaireID in (SELECT luminaireID from LuminairesRegionStaticMap where RegionStaticMapID = 11)


    select LuminiareRegionStaticMapID, RegionStaticMapID, LuminaireID, XPosition, YPosition FROM dbo.LuminairesRegionStaticMap
    where RegionStaticMapID = 11
    Monday, February 26, 2007 7:11 PM

All replies

  • Your table must contain at least two rows where the LuminaireID value is the same but where at lease one row contains a RegionStaticMapID value of 11 and where at least one row's RegionStaticMapID value does not equal 11.

    For example:

    Row     LuminaireID RegionStaticMapID

    1       29          3

    2       42          4

    3       31          11

    4       31          12

    5       47          19

     

    In the above example your first query would return rows 3 and 4 whereas your second query would return only row 3.

    Chris

     

    Monday, February 26, 2007 7:52 PM
  • Asume that you have table:
    LuminaireID RegionStaticMapID
    1                     11
    1                     12
    2                     11
    Subquery in first query select rows #1 and #3 => LuminaireID values 1 and 2. Then external query selects all rows where LuminaireID equlas 1 or 2 => you get row #1, #2, #3.
    Monday, February 26, 2007 7:54 PM
  • Thanks for the replies, guys.  I discovered the same thing and was just coming back to post my answer.

    Cheers.
    Monday, February 26, 2007 7:59 PM