FTS right join in view with ISNULL

Question

I am trying to create View with a left (or right) join for Full-Text Search, and obviously you cannot. I have tried to join using IsNull, but not working.

How would I create a query, that would go in a view that has a right/left join for FTS?

CREATE TABLE #Tline
(
  Id int Primary Key, 
  [name] varchar(255) null
)
GO

Insert into #Tline (id,name) Values(1,'test 1')
Insert into #Tline (id,name) Values(2,'test 2')
Insert into #Tline (id,name) Values(3,'test 3')
Insert into #Tline (id,name) Values(4,'test 4')
Insert into #Tline (id,name) Values(5,'test 5')
GO

CREATE TABLE #Task (Id int Primary Key, TlineId int null)
GO

Insert into #Task (id,TlineId) Values(1,2)
Insert into #Task (id,TlineId) Values(2,3)
Insert into #Task (id,TlineId) Values(3,4)
GO

SELECT 
  #Tline.Id, #Task.Id
FROM 
  #Tline 
  JOIN #Task ON #Tline.Id = ISNULL(#Task.TlineId,0)
GO

DROP 
  TABLe #Tline
DROP 
  TABLe #Task
GO

If you are trying to create a view, I would expect `CREATE VIEW` in the code. That said, I still don't know what your question is. — Gordon Linoff, Mar 12 '20 at 01:00
I'd expect to see [`contains`](https://learn.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-ver15) in the `on` clause. Aside: FTS doesn't tend to work well with numeric data. ([This](https://stackoverflow.com/questions/24739687/why-is-sql-server-full-text-search-not-matching-numbers) is one example.) — HABO, Mar 12 '20 at 02:49

score 0 · Answer 1 · answered Mar 12 '20 at 01:03

0

If you want all rows from #Tline with matching tasks:

SELECT l.Id, t.Id
FROM #Tline l LEFT JOIN
     #Task t
     ON l.Id = t.TlineId;

That seems like a reasonable query given the data you have provided.

answered Mar 12 '20 at 01:03

Gordon Linoff

1,242,037
58
646
786

Yes that would work in a normal query but won't work when creating a view with unique index. (of course this is for FTS) – Zulander Mar 12 '20 at 01:11

FTS right join in view with ISNULL

1 Answers1