2

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;
}
3
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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