Search code examples
postgresqlinner-join

inner join with LIKE condition

Im using Postgres 11 and I have this query:

select sessions.* 
from sessions 
  INNER JOIN map ON (sessions.customer = map.customer) 
WHERE map.prr @> ARRAY['A4'] 
order by session_start DESC;

Now this gives me results but it is leaving out some customers names. I have in sessions table customers name like : bmw honda bmw-12345 - then this query will only show bmw honda.

In map the customers names are without the -12345 (or whatever numbers) but i need it to use LIKE so if map.customer has bmw and sessions.customer has bmw-888 and bmw it will show both.

I have tried there statements but it does not give me the results I need or gives an error:

roles=# select sessions.* 
from sessions 
INNER JOIN map ON map.customer LIKE '%' + CONCAT(CONCAT('%',sessions.customer),'%') + '%' 
WHERE map.prr @> ARRAY['A4'] 
order by session_start DESC;

ERROR:  operator does not exist: unknown + text
LINE 1: ..._sessions INNER JOIN map ON map.customer LIKE '%' + CONCAT(C...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

select sessions.* from sessions INNER JOIN map ON (sessions.customer LIKE map.customer) WHERE map.prr @> ARRAY['A4'] order by session_start DESC;

select sessions.* from sessions INNER JOIN map ON (map.customer LIKE sessions.customer) WHERE map.prr @> ARRAY['A4'] order by session_start DESC;

End result now:

       session_start        |        session_end         |    duration     | customer 
----------------------------+----------------------------+-----------------+----------
 2020-12-28 09:39:15.477601 | 2020-12-28 09:39:48.80666  | 00:00:33.329059 | Honda    
 2020-12-28 09:31:37.841083 | 2020-12-28 09:31:44.154253 | 00:00:06.31317  | Honda    
 2020-12-28 09:29:50.399863 | 2020-12-28 09:30:35.259193 | 00:00:44.85933  | bmw

Results should be:

       session_start        |        session_end         |    duration     | customer 
----------------------------+----------------------------+-----------------+----------
 2020-12-28 09:39:15.477601 | 2020-12-28 09:39:48.80666  | 00:00:33.329059 | Honda    
 2020-12-28 09:31:37.841083 | 2020-12-28 09:31:44.154253 | 00:00:06.31317  | Honda    
 2020-12-28 09:29:50.399863 | 2020-12-28 09:30:35.259193 | 00:00:44.85933  | bmw 
 2020-10-06 14:08:21        | 2020-10-06 14:08:23.109767 | 00:00:02.109767 | bmw-775648
 2020-10-06 13:48:44        | 2020-10-06 13:48:45.950197 | 00:00:01.950197 | honda-775648
Solution

  • The string concatenation operator in SQL is || the + sign is for adding numbers. But you already have the correct expression with your inner most concat() expression. Just remove the other useless things:

    JOIN map ON map.customer LIKE CONCAT('%',sessions.customer,'%')

    Note this only addresses the syntax error. It's unclear to me if your data actually matches this expression. The whole data model seems to be wrong to begin with.