CODEWITHSUNDEEP
sqlsql-serverstringleft-joinsql-like
user4061849
user4061849

Reputation:

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

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.

Upvotes: 1

Views: 922

Answers (1)

Mureinik
Mureinik

Reputation: 311468

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

Upvotes: 2

Related Questions