Question

Partial Match on Inline Querier

  • 20 April 2021
  • 5 replies
  • 37 views
D

Hi, I am trying to perform a LEFT JOIN inside InlineQuerier.

I have two files with similarly named tables, (except for a _).

Inside my TABLE A, in the "CITYCODE" column, I have some values, for example: 

"LATAM - RX12345 - SIE0950331501 - Monclova - CITY SERVICE"

On the other hand, in my TABLE B, in the column of "_CITYCODE" with the value "Monclova".

 

Just like this case, there are thousands more in my tables with the same structure, what I want is to be able to do a LEFT JOIN that partially matches a complete text string containing the values of my column "_CITYCODE".

 

I have tried to do a LEFT JOIN like this:

 

---------------------------------------------------

select * from "TABLE A" 

LEFT JOIN "TABLE B"

 

on "CITYCODE" like '%'+"_CITYCODE "+'%'

--------------------------------------------------

 

But I don't get the '%' as a wildcard for a partial match.

 

Could someone help me with this?

 

 

5 replies

takashi
Rank
Userlevel 2
Badge +17

Hi @diello​ , I think you have to:

- use || operator to concatenate strings

- qualify column name with a table name or its alias.

 

Example:

select * from "TABLE A"
LEFT JOIN "TABLE B"
on "TABLE A"."CITYCODE" like '%'||"TABLE B"."_CITYCODE"||'%'

or

select * from "TABLE A" as a
LEFT JOIN "TABLE B" as b
on a."CITYCODE" like '%'||b."_CITYCODE"||'%'

 

D

Hi @diello​ , I think you have to:

- use || operator to concatenate strings

- qualify column name with a table name or its alias.

 

Example:

select * from "TABLE A"
LEFT JOIN "TABLE B"
on "TABLE A"."CITYCODE" like '%'||"TABLE B"."_CITYCODE"||'%'

or

select * from "TABLE A" as a
LEFT JOIN "TABLE B" as b
on a."CITYCODE" like '%'||b."_CITYCODE"||'%'

 

it works so well.

Perform multiple left joins in a query, could you tell me how to do it?

D

@Takashi Iijima​ Hello again, now I have a problem, the _CITYCODE column does not always have data, and many times the field is empty, how could I solve the LEFT JOIN performing it only when it has information?

takashi
Rank
Userlevel 2
Badge +17

@Takashi Iijima​ Hello again, now I have a problem, the _CITYCODE column does not always have data, and many times the field is empty, how could I solve the LEFT JOIN performing it only when it has information?

A quick way is to insert a Tester before the TABLE B port of the InlineQuerier in order to filter out empty _CITYCODE​.

 

insert-tester-before-inlinequerier 

M
Badge +2

InlineQuerier uses SQLite as the underlying database. So you have to use SQLite compatible SQL. Great resources on SQLite are SQLite Tutorial & SQL As Understood By SQLite

There are also some tips n' ticks on data joins and InlineQuerier in the webinar Data Integration Basics: Merging & Joining Data

 

 

 

Reply


Community Stats

28,729
Posts
109,527
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.