Join Query not including desired information

Hi,

i am struggling with getting my query to include desired data, basicly i want to JOIN tag with document when the query finds a match with TAG and if document title in CLIENT_Document contains the tag_no. its working perfectly when it finds an exact match however some of the data in TAG column is prefixed with 60 and is exluding that data. i have tried making a case statement with substr or regex replace but i havent been able to make it work. Would appreciate help from anyone , attached is a picture that shows desired data from tables and result/ desired result

SELECT DISTINCT

tag.project_id,
tag.tag_no,
tag.fac_suffix,
tag.tag_suffix ,
technical_document.project_no AS "PROJ_DOC_PROJ_NO",
technical_document.doc_no AS "PROJ_DOC_DOC_NO",
client_document.doc_title,
tag.func_domain_id

FROM technical_document, tag 

JOIN client_document ON client_document.doc_title LIKE  '%'||TAG_NO||'%'



WHERE tag.project_no LIKE '%029P%'
AND client_document.doc_no LIKE '%'||technical_document.doc_no||'%'

Please provide:

  • table definitions in the form of create statements
  • sample data in the form of insert statements
  • expected result from the sample data you provide
  • name and version of your database engine
1 Like