SQL. Defining a parameter by a column from another table

user_19347093 0
Viewed 32

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?

postgresql sql
1 Answers

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
user_58768030
Related