-1

I have two tables:

regional

enter image description here

imd

enter image description here

The regional contains a location column with information about cities of UK. The imd table contains a column 'Name' which also includes the city names.

I want to join both tables based on the location and Name but in regional table, the location also includes the country name whereas the imd table only contains the city name. I want to join tables based on partial string match I've tried the following code but it is not working.

SELECT *
FROM s.regional, s.imd
WHERE s.regional.location like concat('%',s.imd.Name)
Sannia Nasir
  • 1
  • 2
  • Tag your question with the database you are using. Explain what "not working" means. Whey are you choosing not to use modern, explicit, **standard**, readable `JOIN` syntax? – Gordon Linoff Aug 04 '20 at 16:18

1 Answers1

0

You could take the first part of the [location] field in the [regional] table before the comma and join it to the [name] column in the [imb] table like this:

SELECT a.*,b.*
FROM s.regional a LEFT JOIN s.imb b ON SPLIT_PART(a.location, ',', 1)  = b.[name]

Ideally you should move the [location] field into it's own table where you can have columns like city, Country etc and create a reference to that table (foreign key). It will make it easy to maintain(Insert/Update/Delete) you tables in the future.

jmm312
  • 618
  • 1
  • 6
  • 17
  • I'm using pgadmin 4. It gives the error ERROR: function charindex(unknown, text) does not exist. HINT: No function matches the given name and argument types. You might need to add explicit type casts. – Sannia Nasir Aug 04 '20 at 17:06
  • I have updated the syntax to match functions found in PostGreSQL (SPLIT_PART) – jmm312 Aug 04 '20 at 17:13
  • This works thank you! But for some observations such as London, UK in the regional table, if I am matching it against the imd table which has "City of London", it wont match. Is there a way in which I can use a LIKE operator to do it? – Sannia Nasir Aug 04 '20 at 17:49
  • If there are just a handful of those instances you could use the REPLACE to strip out the first part: SPLIT_PART(a.location, ',', 1) = REPLACE (b.NAME, 'City of ', ''). Also mark as an answer if this was helpful. – jmm312 Aug 04 '20 at 18:00