Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Join creating two records Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 3:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,796, Visits: 5,799
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1450819
    Posted Thursday, May 9, 2013 2:36 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: 2 days ago @ 8:55 AM
    Points: 2,873, Visits: 5,185
    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!"
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help
    Post #1450933
    Posted Thursday, May 9, 2013 7:04 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Thursday, October 9, 2014 1:19 PM
    Points: 327, Visits: 587
    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.
    Post #1451097
    Posted Friday, May 10, 2013 10:23 AM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Yesterday @ 7:34 AM
    Points: 1,061, Visits: 2,574
    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
    Post #1451682
    Posted Monday, May 13, 2013 8:28 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Thursday, October 9, 2014 1:19 PM
    Points: 327, Visits: 587
    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.
    Post #1452132
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse