SQL join statement on 2 tables

Question

i am creating a join between two tables Product_Tree and Product on the two columns Model_Number and Manufacturer to return the matching columns.

Here are the 2 tables:

Table : Product_Tree

Col1,Col2,Model_Number,Col3,Manufacturer,Col4
111111,Pepsi,aaa,111111,aaa,description
222222,Miranda,bbb,222222,bbb,'description
333333,Cola,bbb,333333,bbb,description

Table : Product

Model_Number,Manufacturer
a,a
b,b
c,c
d,d

Here is the query:

SELECT Product_Tree.col0,Product_Tree.col1,Product_Tree.col2,Product_Tree.col3,Product_Tree.Model_Number,Product_Tree.Manufacturer 
FROM Product_Tree 
JOIN Product ON Product.model_number LIKE ''''%''''Product_Tree.MODEL_NUMBER''''%'''' 
AND Product.manufacturer LIKE ''''%''Product_Tree.MANUFACTURER''''%'''';

I am getting this error:

ORA-00911: invalid character

Answer 1

You'll need to use a concatenation operator to concatenate your % wildcard to your column product_tree.manufacturer:

SELECT Product_Tree.col0,Product_Tree.col1,Product_Tree.col2,Product_Tree.col3,Product_Tree.Model_Number,Product_Tree.Manufacturer 
FROM Product_Tree 
JOIN Product ON Product.model_number LIKE '%' || Product_Tree.MODEL_NUMBER || '%' 
AND Product.manufacturer LIKE '%' || Product_Tree.MANUFACTURER || '%';

I'm guessing that this query is inside a script and is quoted using single quotes ' which is why you have single quotes all over the place in here. If that's the case then your quoted SQL statement would be:

SELECT Product_Tree.col0,Product_Tree.col1,Product_Tree.col2,Product_Tree.col3,Product_Tree.Model_Number,Product_Tree.Manufacturer 
FROM Product_Tree 
JOIN Product ON Product.model_number LIKE ''%'' || Product_Tree.MODEL_NUMBER || ''%'' 
AND Product.manufacturer LIKE ''%'' || Product_Tree.MANUFACTURER || ''%'';