Can someone please help me with a SQL query? My apologies if a similar question has been asked before. Finding it difficult from other examples I have seen.
I have 2 tables and would like to create a third table.
Table – advisories
x
advID | productName
1 | 3.4/3.5/3.6
2 | 3.4
3 | 3.5/3.6
Table – customerA
hostname | version
A | 3.3
B | 3.5
C | 3.6
Final Table
hostname | advID
A | NULL
B | 1
B | 3
C | 1
C | 3
Does this look correct?
select advId, productNames, hostname, version
from advisories, customerA
where productNames
like '%3.6%';
Thanks for the help.
Advertisement
Answer
I would suggest using the LIKE
in the JOIN
as follows:
SELECT C.HOSTNAME,
A.advisoryId
FROM customerA C LEFT JOIN advisories A
ON CONCAT('/',A.productNames,'/') LIKE concat('%/',C.version,'/%')
order by C.HOSTNAME
See SQL Fiddle. I have changed the ||
to CONCAT
function.