Assume the following layout in a MySQL database

Activity (id,etc) [id is primary key]
AcitivityMapper (activityID, targetYearID)  [composite key]
Years (targetYearID, targetYearName) [id primary key, name is unique]

I would like to, given a list of "year names", find the activities that can be mapped to at least one of the year names

Extra notes: using the above layout, an activity can be mapped to multiple years


  • Activity (1, bla1)
  • Year (1, year1)
  • Year (2, year2)
  • Year (3, year3)
  • Mapper( 1, 1)
  • Mapper (1, 2)

If given a list containing "year1" or "year2" i want to get activity 1

How would I do this using an SQL query?

Extra Note:

I'm using hibernate in java on spring. The objects I have are as follows:

My objects would be as follows:

@Entity @Table(name="Activity")
class Activity {
 @Id @Column(name="id") private Integer id;
 @ManyToMany @JoinTable(name="ActivityMapper",joinColumns={@JoinColumn(name="id")},inverseJoinColumns{@JoinColumn(name="targetYearID")}) private List<TargetYear> targetYears = new ArrayList<>();

@Entity @Table(name="Years")
class TargetYear {
 @Id @Column(name="targetYearID") private Integer id;
 @Column(name="targetYearName") private String name;

1 Answer 1

FROM years y
JOIN activitymapper am
   ON (y.targetyearid = am.targetyearid)
JOIN activity a
   ON (a.id = am.activityid)
WHERE y.targetyearname IN ('Year1', 'Year2')

Should do it. We're going from years to activity through activitymapper, selecting each activity only once (DISTINCT).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.