Eugene Elutin (5/8/2013)
dwilliscp (5/8/2013)
I have a join that is doing something that I can not explain.I have two tables.. linking on Delivery No and Item No..
Temp table #Delivery has one record with Delivery No = 00835308 and the second table does not have that Delivery at all. But I get two records running...
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
Like gives only one record.. but when I run without the WHERE it never completes the INSERT.
Using LEFT JOIN means that as long as record(s) with "cast(delivery as int) = 83535308" exists in #Delivery, these records will be selected regardless of anything existing or not existing in your "details_2" table.
What do you mean by " I run without the WHERE it never completes the INSERT."?
If you remove your WHERE clause, all records from #Delivery will be selected.
If your "details_2" table contains more than one matching record for a single record in #Delivery, than you should expect the same row from #Delivery to be returned as many times as may matches found in "details_2"
Since the first table had one record with a delivery of 00835308 and the second had none, I would have expected the SQL to return one record. What I got was two records that are the same.
The code I have placed here.. shows the problem, but the WHERE was added to show why my query is failling with a PK failure.... and the Insert Into was removed.