PostgreSQL – Column Containing Word from Another Column

postgresqlpsql

I am trying to select records in a postgresql where the service column contains usernames.

Something like this but not 1 to 1:

select service, cash from services where service not in (select name from users);

and this but for all users:

select service, cash from services join users on service not like '%' || 'username' || '%' and user = id_users;

this show me all records from services:

 select service, cash from services join users on service not like '%' || name || '%' and user = id_users;

example:

    CREATE TABLE users (
    id_users SERIAL PRIMARY KEY,
    name character varying(50) NOT NULL
);

CREATE TABLE services (
    service text,
    cash real,
    usery integer REFERENCES users (id_users)
);

insert into users (name) 
values ('user1'),('user2'),('user3'),('user4');
select * from users;

insert into  services (service, cash, usery) 
values ('user1','123','1'),
       ('something user2 something','123','1'),
       ('something user3 something','123','3'),
       ('something something','123','1'),
       ('fhalsefh','123','4');
select * from services;

I expect:

service              |  cash  |  usery
---------------------------------------
something something  |   123  |    1
fhalsefh             |   123  |    4

Best Answer

From your sample data I guess something like:

select * from services x
where not exists (
   select 1 from users y
   where x.service like '%' || y.name || '%'
);

should do