I'm having a bit of trouble with a SQL query. I'm using MS SQL 2005 Enterprise. I'm attempting to return records from two tables in cases where values in table1.field1=values in table2.field1+a wildcard. For example, if table1.field1 contains record 5478, I'd like to return values in every case where records in table2.field1 contain that value, say records 5478-01, 5478-04, 5478-06, etc. I have experimented with different kinds of joins and placements of wildcards, but, so far, I've only been able to get returns where identical values are present in both tables. For example:
select gl20000.orctrnum, rm30201.apfrdcnm from gl20000
on gl20000.orctrnum+'%' like '%'+rm30201.apfrdcnm+'%'
only returns records where identical records exist in both tables. Moving/removing wildcards has no effect, which leads me to believe I'm missing something really obvious. Using, say, left join just gives me all records in table1 with nulls where non-identical values exist in table two. There are tons of records that satisfy these conditions, so...
Okay, any help you guys can provide would be awesome. Thanks!