I have the below query which combines the child table records as comma separated strings. But when i try applying filters to the correlated subquery, values be replaced as null instead of removing the record.
Parent Table : Employee
Id | Name |
---|---|
I1BF91860BCB711EDBF4EF0FBCBCA9E17 | Tim |
I1BF91860BCB711EDBF4EF0FBCBCA9E18 | Sam |
I1BF91860BCB711EDBF4EF0FBCBCA9E19 | Rohan |
Child Table: Trainings
Id | Name | Employee Id |
---|---|---|
I1BF91860BCB711EDBF4EF0FBCBCA9P10 | Java Basics | I1BF91860BCB711EDBF4EF0FBCBCA9E17 |
I1BF91860BCB711EDBF4EF0FBCBCA9P11 | Angular | I1BF91860BCB711EDBF4EF0FBCBCA9E17 |
I1BF91860BCB711EDBF4EF0FBCBCA9P12 | DotNet | I1BF91860BCB711EDBF4EF0FBCBCA9E18 |
Below is the query which results in the output as shown below
select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id")
FROM trainings tr WHERE e."Id" = tr."Employee Id")
trainingss
from employee e
output:
As shown in the image, child values are grouped as comma separated string as expected, but i am having issues while filtering the child values.
How to apply filters on child column values with the subqueries used. Say i wanted to display rows with JavaBasics and Angular, and expected should be first row. But in the below output image i could see all the rows being displayed with Null values.
What i tried?
select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id")
FROM trainings tr WHERE e."Id" = tr."Employee Id" and tr."Id" = 'I1BF91860BCB711EDBF4EF0FBCBCA9P10')
trainingss
from employee e
Output:
Please note, above example is just a sample of what our requirement is and in our real case scenarios we have more than 150 columns and all are dynamic. Hence we don't want to use GroupBy
You can use exists for every condition or count the empoyees that have the number of trainings if they are exactly like the number training you search
select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id")
FROM trainings tr WHERE e."Id" = tr."Employee Id"
)
trainings
from employee e
WHERE EXISTS (SELECT 1 FROM trainings tr1 WHERE e."Id" = tr1."Employee Id"
AND tr1."Id" IN ('I1BF91860BCB711EDBF4EF0FBCBCA9P10'))
ID | NAME | TRAININGS |
---|---|---|
I1BF91860BCB711EDBF4EF0FBCBCA9E17 | Tim | Java Basics,Angular |
select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id")
FROM trainings tr WHERE e."Id" = tr."Employee Id"
)
trainings
from employee e
WHERE EXISTS (SELECT 1 FROM trainings tr1 WHERE e."Id" = tr1."Employee Id"
AND tr1."Id" IN ('I1BF91860BCB711EDBF4EF0FBCBCA9P11'))
AND EXISTS (SELECT 1 FROM trainings tr1 WHERE e."Id" = tr1."Employee Id"
AND tr1."Id" IN ('I1BF91860BCB711EDBF4EF0FBCBCA9P10'))
ID | NAME | TRAININGS |
---|---|---|
I1BF91860BCB711EDBF4EF0FBCBCA9E17 | Tim | Java Basics,Angular |
select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id")
FROM trainings tr WHERE e."Id" = tr."Employee Id"
)
trainings
from employee e
WHERE e."Id" IN
(SELECT "Employee Id" FROM trainings
WHERE "Id" IN ('I1BF91860BCB711EDBF4EF0FBCBCA9P10','I1BF91860BCB711EDBF4EF0FBCBCA9P11')
GROUP BY "Employee Id"
HAVING COUNT(*) = 2)
ID | NAME | TRAININGS |
---|---|---|
I1BF91860BCB711EDBF4EF0FBCBCA9E17 | Tim | Java Basics,Angular |