So, I have a table that has 3 things: a primary key, table_id, foreignkey_id and doctor_id. - table_id refers to a table with a certain number. - foreignkey_id refers to primary key within that table. - doctor_id refers to a primary key in doctor table Now, the user will select which tables he wants to use and which items in that table he wants. I need all the doctor_id's that match the combinations.
CREATE TABLE tConfidentiality(
confidentiality_id serial primary key,
table_id integer not null,
doctor_id integer not null,
foreignkey_id integer not null,
constraint un_confidentiality unique(table_id, doctor_id, foreignkey_id)
);
INSERT INTO tConfidentiality(table_id, doctor_id, foreignkey_id) values(10, 100, 1000);
INSERT INTO tConfidentiality(table_id, doctor_id, foreignkey_id) values(10, 100, 2800);
INSERT INTO tConfidentiality(table_id, doctor_id, foreignkey_id) values(40, 100, 2000);
INSERT INTO tConfidentiality(table_id, doctor_id, foreignkey_id) values(80, 110, 2500);
INSERT INTO tConfidentiality(table_id, doctor_id, foreignkey_id) values(90, 120, 2800);
Query: The values have to be parameterized
All doctor_id in table 10 with foreignkey_id 1000 & 2800 and table 40 with foreignkey_id 2000
Expected result: 100
Don't expect you to write the entire thing, just point me in the right direction and I'll take it from there.
You need to collect the foreign key ids for each table_id and then check if the contain at least those that you are looking for. In Postgres this can be done by aggregating them into an array:
select distinct doctor_id
from tConfidentiality
group by doctor_id, table_id
having table_id = 10 and array_agg(distinct foreignkey_id) @> array[1000,2800]
or table_id = 40 and array_agg(distinct foreignkey_id) @> array[2000];
online example: https://rextester.com/NHE4869