Home PostgreSQL subquery using like
Reply: 2

PostgreSQL subquery using like

thekucays
1#
thekucays Published in 2018-01-12 11:25:28Z

lets say i have first table like this

Branch table

|name     |description|
|123456ABC|FOO        |
|553646DEF|FO2        |

and second table like this

Balance table

|name|description|
|ABC |oof        |
|DEF |2of        |

i want to query to Balance table, where each row containing name from Branch table.. for example "123456ABC" in Branch table, i want to get "ABC" row from Balance table

how could i achieve that? i've tried this query so far with no luck

select * from Balance
where name like (
        SELECT `name` FROM Branch
);

any suggestion?

clemens
2#
clemens Reply to 2018-01-12 11:34:35Z

You should convert the balance's names to LIKE patterns:

SELECT * FROM Balance
WHERE (
    SELECT `name` FROM Branch
) LIKE '%' || name;

A join may look more readable:

SELECT b.* FROM Balance b JOIN Branch r ON r.name LIKE '%' || b.name;
Hambone
3#
Hambone Reply to 2018-01-12 11:50:34Z

I don't know if you will have dupes or not, so you may want to consider using a semi-join. For large datasets, a semi-join will typically be more efficient than an in-list query.

@clemens solution looks good, assuming no dupes. Alternatively, you can use regex:

select *
from balance ba
where exists (
  select null
  from branch br
  where
    br.name ~ ba.name
)

Performance-wise, I think like will outperform the regex, but it's an option.

Also, if your string is always at the end, you can consider a join using right or substr:

select *
from balance ba
where exists (
  select null
  from branch br
  where
    right (br.name, length (ba.name)) = ba.name
)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.301934 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO