0

Can anyone please guide me what's wrong in this SQL Server query:

SELECT 
    A.*,
    (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) Name 
FROM A;

It seems that A.Name is inaccessible in the query.

I want to populate a column in select query with data from another table. I can't use INNER JOIN since there isn't any FK relationship between tables.

Actually I want to insert data from table A into another table C, but for insert I need to populate a NOT NULL Column in table C, that I am trying to populate with this query (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) as column.

Any help would be really appreciated.

9
  • 1
    You can use a join in T-SQL against any columns you want. If the B.Name and A.Name columns match, it doesn't matter if there is no foreign key. Feb 18 at 13:29
  • Will it work in case of Full Text Search. And what if there are multiple matching rows in table B? Feb 18 at 13:37
  • Is B full-text indexed?
    – McNets
    Feb 18 at 13:54
  • I tried SELECT * FROM A INNER JOIN B ON Contains(B.[Name],A.[Name])); But even this doesn't work and complaints Incorrect syntax near *A.* Expecting string TEXT_LEX or Variable Feb 18 at 13:56
  • Yes B is full text indexed. Feb 18 at 13:57
0

The problem is just as the error message explains. The second argument of CONTAINS must be a literal string or a variable. It’s not the case that everywhere a string is allowed in T-SQL that you can also use a column name.

You could do something kludgy like this, but performance would likely be abominable.

CREATE FUNCTION DoesContain(@arg VARCHAR(30))
RETURNS tinyint AS BEGIN
  RETURN
    CASE WHEN EXISTS (
      SELECT * FROM B
      WHERE CONTAINS([Name],@arg)
    ) THEN 1 ELSE 0 END
END;
GO

SELECT
  A.*,
  (SELECT TOP 1 B.Name FROM B WHERE dbo.DoesContain(A.Name)=1) as [Name]
FROM A;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.