MySQL – Querying Over a Join with List Membership

hibernatejavaMySQL

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

Example:

  • 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;
}

Best Answer

SELECT DISTINCT
   a.id,
   a.etc
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).

Related Question