SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Join creating two records


Join creating two records

Author
Message
mister.magoo
mister.magoo
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9933 Visits: 7891
dwilliscp (5/8/2013)
Lynn Pettis (5/8/2013)
dwilliscp (5/8/2013)
...
I have found one other strange thing..
The outer join has data in the columns for details_2, so I do not understand how in the heck it is doing the link between the two tables... there shouldn't be anything on the details_2 side.


Your query in the original post is SELECT * ..., this means you are going to see the all the columne from both tables used in the query.


True, but since this Delivery only exists in one table I would expect the columns related to the Details_2 to be NULL, but they are not.


So, if the columns being displayed from details_2 are not NULL, what values are displayed in details_2.Delivery_Doc and details_2.Delivery_Item ?

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Eugene Elutin
    Eugene Elutin
    SSChampion
    SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

    Group: General Forum Members
    Points: 11642 Visits: 5478
    dwilliscp (5/8/2013)
    Sean Lange (5/8/2013)
    What are the results of these two queries?


    select COUNT(*) FROM #Delivery Where cast(delivery as int) = 83535308

    select COUNT(*) from details_2 where cast(details_2.Delivery_Doc as int) = 83535308



    One (Delivery) and zero (details_2)... that is what makes this so darn strange. If you do an inner join you come up with nothing.

    I have found one other strange thing..
    The outer join has data in the columns for details_2, so I do not understand how in the heck it is doing the link between the two tables... there shouldn't be anything on the details_2 side.


    Based on what you have in result of tqo COUNT queries your posted query of

    select *
    FROM #Delivery Left Outer JOIN
    details_2 ON cast(#Delivery.Delivery as int) = cast(details_2.Delivery_Doc as int) AND
    cast(#Delivery.Delivery_Item as int) = cast(details_2.Delivery_Item as int)
    Where cast(delivery as int) = 83535308


    will return one row only.
    So, I guess you posted not exact query you are running (probably you have omitted some things which you might think were insignificant).

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help
    dwilliscp
    dwilliscp
    SSCrazy
    SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

    Group: General Forum Members
    Points: 2027 Visits: 784
    The only things I left out was the listing of columns and the complete name (liked server.database.dbo.table), and the WHERE includes a limit on the #delivery... "Where #Delivery.Load_From = 'Something' ".

    I am not sure why I did not find this yesterday... but when I run the following query today I find two records:

    select *
    from details_2
    where cast(delivery_doc as int) = 83535308

    so now I get to find out why there are two records here .. that look the same.
    wolfkillj
    wolfkillj
    SSCrazy
    SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

    Group: General Forum Members
    Points: 2568 Visits: 2582
    dwilliscp (5/9/2013)
    The only things I left out was the listing of columns and the complete name (liked server.database.dbo.table), and the WHERE includes a limit on the #delivery... "Where #Delivery.Load_From = 'Something' ".

    I am not sure why I did not find this yesterday... but when I run the following query today I find two records:

    select *
    from details_2
    where cast(delivery_doc as int) = 83535308

    so now I get to find out why there are two records here .. that look the same.


    Do you have more than one schema in your database? If there are tables named [details_2] in more than one schema, the fact that you have used an unqualified table name means that SQL Server may be resolving the table name to a schema other than the one you expect.

    Jason Wolfkill
    Blog: SQLSouth
    Twitter: @SQLSouth
    dwilliscp
    dwilliscp
    SSCrazy
    SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

    Group: General Forum Members
    Points: 2027 Visits: 784
    Most, but not all, are dbo... but this table exists only in dbo. I think what I might have done was not to cast delivery in the where statement... the first day... at least that is the only thing I can think would explain the null record set when I was looking for where I expected problem to be.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search