CODEWITHSUNDEEP
javasqloracle-databasejpa
ChrisZ
ChrisZ

Reputation: 502

Dynamic attribute name search

Consider I have a student table like this:

student_id   name   address ...   school   employer
    1      Chris    2 John         UofJ     J Limited
    2      Ann      3 Doe          UofD     D limited

Right now I need to find a list of students who have school = 'UofJ' and employer = 'J Limited'. Easy:

select * from student where school = 'UofJ' and employer = 'J Limited'

However, my reality is the last 2 attributes are stored in student table as columns but in a separate table called student_attribute as rows:

student_attribute_id    student_id    attribute_name    attribute_value
       1                    1            school            UofJ
       1                    1            company           J Limited
       1                    2            school            UofD
       1                    2            company           D Limited

My task is to find a list of student IDs from this student_attribute table still based on school = 'UofJ' and employer = 'J Limited'. How should I do it?

Moreover, I am using Springboot JPS repository to do the query, so I am willing to listen to solution to both a sql way or JPA way.

Upvotes: 0

Views: 107

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can use conditional aggregation to find out which student_id has both the conditions true.

select student_id
from student_attribute
group by student_id
having count(case 
            when attribute_name = 'school'
                and attribute_value = 'UofJ'
                then 1
            end) > 0
    and count(case 
            when attribute_name = 'company'
                and attribute_value = 'J Limited'
                then 1
            end) > 0

You can then maybe join it with the student table to get the corresponding details.

select s.*
from student s
join (
    select student_id
    from student_attribute
    group by student_id
    having count(case 
                when attribute_name = 'school'
                    and attribute_value = 'UofJ'
                    then 1
                end) > 0
        and count(case 
                when attribute_name = 'company'
                    and attribute_value = 'J Limited'
                    then 1
                end) > 0
    ) a on s.student_id = a.student_id;

Upvotes: 1

Andreas
Andreas

Reputation: 5093

Set up a join for each attribute you care about:

select * from student s
join student_attribute school on school.student_id = s.student_id 
join student_attribute company on company.student_id = s.student_id 
where company.attribute_value='J Limited'
and school.attribute_value='UofJ'

Upvotes: 0

Related Questions