SQL query to select from multiple tables and create third table

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

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.

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.

Advertisement

Answer