UDT (Table) problem with join

[thendrickson]

I have reduced a more complex problem to the simplest elements in the following selects.

my select using a join does not return any data while the select using a sub select returns data as expected

I have researched this forum as well as BOL and am finding nothing that explains where I am off track

Either I have tunnel vision and am missing something very basic or I do not understand a UDT table. Can somebody help?

[blue]

DECLARE @Input dbo.tbl_serial --USER DEFINED type table

--fill UDT with TEST data
Insert into @Input Select distinct T.OriginalUserOrdNo ,T.OriginalRelease, T.Serial from dbo.table1 T1

--Verify test data is present
select * from @input order by serial

--Test SELECT with join fails
select distinct t1.col1, t1.col2, t.Serial
from dbo.Table1 t1
INNER join @Input I
on
t1.Serial = I.Serial

--returns expected records
select distinct t1.col1, t1.col2, t.Serial
from dbo.Table1 t1
where t.Serial in(Select distinct serial from @Input)

[/blue]

 

[bborissov]

You missed one [1]

 --Test SELECT  with join fails
select distinct t1.col1, t1.col2, t[COLOR=red][b]1[/b][/color].Serial
from   dbo.Table1  t1
INNER join @Input  I on t1.Serial = I.Serial

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[thendrickson]

Thanks. This would count at least as an "oops" I guess