Left Join - Row from left table not returned

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Left Join - Row from left table not returned

Author

Topic

MuffinMan
Posting Yak Master

107 Posts

Posted - 2001-06-11 : 16:25:11

I've written a SQL statement with a LEFT JOIN, but I'm not getting all the records from the left table.

The left table, #field, is like a table of metadata: it contains the information from which a table and a view can be constructed. When rendered, the table (#t93) has field names such as C1, C2, C15, C600000226, etc. (i.e., the fieldID column with a preceding "C"). The view uses the fieldName information as the column names for the view (spaces and special characters are replaced with underscores - Request_ID , Submitted_By, etc.)

The datatypes in #field are as follows: 4=varchar; 7=datetime (stored as integer in #t93, it's the number of seconds since 1/1/1970); 5=diary (unlimited text).

Now the application that created this structure (Remedy) allows a developer to specify a varchar field and set the max length equal to zero (0), which means unlimited. So, it's stored in #field as a varchar but in #t93 it's created as a TEXT datatype.

Thus, I want to join #field with the information_schema.columns view to get the correct datatype for all fields.

(That's way too much info, but I'd rather explain it up front so everyone understands the "Why?")

With the left join I expect to get all records from #field but Field ID 15, Status-History does not appear. Why?

{Field ID 15, Status-History, in the #field table doesn't exist in the actual SQL table, #t93 (it's a virtual reference to another table - don't worry about this).}


set nocount on

create table #field ( schemaId int , fieldId int , datatype int , fieldName varchar(40) , fieldType int , fOption int )

insert #field select 93 , 1 , 4 , 'Request ID' , 1 , 2
insert #field select 93 , 2 , 4 , 'Submitted By' , 1 , 2
insert #field select 93 , 3 , 7 , 'Date/Time Requested' , 1 , 2
insert #field select 93 , 15 , 4 , 'Status-History' , 1 , 2
insert #field select 93 , 600000226 , 4 , 'Notes' , 1 , 2


create table #t93 ( c1 varchar(20) , c2 varchar(30) , c3 int , c600000226 text )

print '** #field table **'
select f.fieldId , f.datatype  , lower( f.fieldName ) as fieldName 
from #field as f
Where f.schemaId = 93
  and f.fieldType = 1 
  and f.fOption != 4 
  and f.dataType != 6 
order by f.fieldId 

print ''

print '** #t93 **'
select * from #t93

print ''

print '** left join of #field and #t93 tables - fieldID 15, status-history does not appear **'
select f.fieldId , datatype = case when IsNull(i.Data_Type,'') = 'text' then 5 else f.datatype end, lower( f.fieldName ) as fieldName 
from field as f
left join tempdb.information_schema.columns as i
on 'c' + convert(varchar(20),f.fieldId) = i.column_name
Where f.schemaId = 93
  and f.fieldType = 1 
  and f.fOption != 4 
  and f.dataType != 6 
  and i.table_name like '#t93%' 
order by f.fieldId 

drop table #field , #t93

Here are the results:


** #field table **
fieldId     datatype    fieldName                                
----------- ----------- ---------------------------------------- 
1           4           request id
2           4           submitted by
3           7           date/time requested
15          4           status-history
600000226   4           notes

 
** #t93 **
c1              c2                   c3     c600000226
--------------- -------------------- ------ ---------------------------

 
** left join of #field and #t93 tables - fieldID 15, status-history does not appear **
fieldId     datatype    fieldName                      
----------- ----------- ------------------------------ 
1           4           service request id
2           4           submitted by
3           7           date/time requested
600000226   5           notes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources