left join table on string like '%table.name%'

Question

I am trying to left join products.table and manufacturer.table to implement the manufacturer into the products table

The only problem there is no id linking the tables, so I am trying something like this.

 SELECT [kArtikel]
  ,[cArtNr]
  ,a.[cName]
  ,a.[cBeschreibung]
  ,H.cName
  ,[cKurzBeschreibung]
  ,[cHersteller]
  ,[cHAN]
  FROM [db].[dbo].[tartikel] a
  left join [db].[dbo].[tHersteller] h on a.cName Like '%H.Cname%'
  where cHersteller is null 

But with this query I am recieving only H.cName = Null
After 2 min execute with the same number of products, having cHersteller = Null

While searching I only find examples where join is on some id with where clause. But this is not the possible here.

Answer 1

You are querying whether a.cName contains the literal H.Cname, which is probably not what you were gunning for. If you want to have wildcards around H.Cname, you can use the + operator:

SELECT [kArtikel]
  ,[cArtNr]
  ,a.[cName]
  ,a.[cBeschreibung]
  ,H.cName
  ,[cKurzBeschreibung]
  ,[cHersteller]
  ,[cHAN]
FROM [db].[dbo].[tartikel] a
LEFT JOIN [db].[dbo].[tHersteller] h ON a.cName LIKE '%' + H.Cname + '%'
WHERE cHersteller IS NULL