CODEWITHSUNDEEP
sqlspringspring-data-jpa
Abdurakhmon
Abdurakhmon

Reputation: 3070

Spring query where in () is not working

I am trying to query faculties that have 3 subjects mysql statement is as the following 

select * from faculty_subject where subject_id  in(1 and 3 and 2);

where 1, 2, 3 are subject ids. I only want courses that have all these subjects so that reason I am using and. But I do not why my spring query is not working. I tried different ways but it's returning faculties with the first subject id in this case 1. Here is my spring query

@Query(value = "select * from faculty_subject where subject_id in(?#{[0]} and ?#{[1]} and ?#{[1]})", nativeQuery =  true)
    List<FacultySubject> getFacultyBySubjects(long subjectTop, long subjectMid, long subjectBottom);

another one that did not work

 @Query(value = "select * from faculty_subject where subject_id in(?1 and ?2 and ?3)", nativeQuery =  true)
    List<FacultySubject> getFacultyBySubjects(long subjectTop, long subjectMid, long subjectBottom);

another query

@Query(value = "select * from faculty_subject where subject_id in(:#{#subjectTop} and :#{#subjectMid} and :#{#subjectBottom})", nativeQuery =  true)
List<FacultySubject> getFacultyBySubjects(@Param("subjectTop") long subjectTop,
                                          @Param("subjectMid") long subjectMid,
                                          @Param("subjectBottom") long subjectBottom);

Entity

@Entity
public class FacultySubject {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    private long facultyId;

    private int subjectId;

    public FacultySubject(long facultyId, int subjectId) {
        this.facultyId = facultyId;
        this.subjectId = subjectId;
    }

    public FacultySubject() {
    }
... getters and setters

Here is my table

Upvotes: 0

Views: 714

Answers (1)

StanislavL
StanislavL

Reputation: 57381

@Query(value = "select * from faculty_subject where subject_id in(?1 , ?2 , ?3) group by faculty_id having count(*)==3", nativeQuery =  true)
List<FacultySubject> getFacultyBySubjects(long subjectTop, long subjectMid, long subjectBottom);

Upvotes: 1

Related Questions