0

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
2
  • 2
    Can you create a dbfiddle.uk with tables and sample data that demonstrates the problem? May 2, 2020 at 16:39
  • I created the tables, I hope it was about that
    – slawekh666
    May 2, 2020 at 17:49

1 Answer 1

0

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

1
  • Thanks very much, now we'll see if it works with the rest of the query
    – slawekh666
    May 2, 2020 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.