Query help

sidsel

I have these three tables in my database and because of a table (HNV) with sometimes not so logic productdescriptions I want to make a seperate table with extra keywords.

Main table with item nr. and not-so-logic product descriptions (can't be altered on my end).

HNV:

itemnr | productdescription
2424 | some text
3030 | some other text
4040 | text text

WEBGROUPS:

itemnr | webgrp
2424 | 401000
3030 | 502000
4040 | 502000

KEYWORDS:

webgrp | search
401000 | word1 word2 word3
502000 | word4 word 5

Right now my query looks like the one below:

SELECT KEYWORDS.Webgrp, HNV.itemNr, HNV.productdescription FROM WEBGROUPS INNER JOIN KEYWORDS ON WEBGROUPS.Webgrp = KEYWORDS.Webgrp INNER JOIN HNV ON WEBGROUPS.itemNr = HNV.itemNr
WHERE KEYWORDS.search LIKE '%word1%'

If a user search for something the query will look in 'KEYWORDS.search' for any matching words. But how do I expand the query above to also look in 'HNV.productdescription' for any match?

rincewind456

Something like

WHERE KEYWORDS.search LIKE '%word1%' OR HNV.productdescription LIKE '%word1%'

sidsel

rincewind456 wrote:

Something like

WHERE KEYWORDS.search LIKE '%word1%' OR HNV.productdescription LIKE '%word1%'

I have tried it and it doesn't work. Probably because of the way the joins are formatted.

tomhath

Should work if you search for %some% or %text% in ProductDescription. Obviously %word1% won't find anything.

sidsel

tomhath wrote:
Should work if you search for %some% or %text% in ProductDescription. Obviously %word1% won't find anything.

I am aware of that.
The query only works if a itemnr is listed in both table HNV and table WEBGROUPS because of the joins.

My intention was to format a query that search in both text fields even though not all itemnr from table HNV are listed in table WEBGROUPS.