SQL. Defining a parameter by a column from another table-CodePudding

Tab 1:

id	address
01	Sumskoy street
02	Sumskoy passage
03	Long street
04	1st ave

Tab 2:

name
Sumskoy
1st

I need to create a query that will return the same table with the addition of a new column with a condition met:

if "Tab1.address" matches the "Tab2.name",
then value is set to 1
otherwise value is set to 0.

Expected output:

address	new_column
Sumskoy street	1
Sumskoy passage	1
Long street	0
1st ave	1

Attempted query:

SELECT
    tab1.adress, 
    CASE WHEN tab1.adress LIKE ('%' || tab2."name" || '%') then '1' else '0' end as New_column
FROM
    tab1,
    tab2

Current (wrong) output:

address	New_column
Sumskoy street	1
Sumskoy passage	1
Long street	0
1st ave	0
Sumskoy street	0
Sumskoy passage	0
Long street	0
1st ave	1

Can you help me with this problem?

CodePudding user response：

Just add MAX to your CASE and GROUP BY tab1.adress

SELECT
tab1.adress, 
MAX(CASE WHEN tab1.adress LIKE ('%' || tab2."name" || '%') then '1' else '0' end) as New_column
FROM
tab1,
tab2
GROUP BY tab1.adress

CodePudding user response：

You can use a LEFT JOIN operation, move your condition from the SELECT statement to the JOIN condition, and use a LIKE statement to translate your condition ("if the "address" column of "tab 1" matches the "name" column from the "tab 2""). Then you can use a CASE statement to transform matches into ones and non-matches (NULL values due to left join) to zeros.

SELECT tab1.address,
       CASE WHEN tab2.name IS NULL THEN 0 ELSE 1 END AS new_column
FROM tab1
LEFT JOIN tab2
       ON tab1.address LIKE CONCAT('%',tab2.name,'%')

Check the demo here.