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:
should do