I'm a bloody beginner (working with Access). Suppose that I have two tables with data:

1. list of students
2. list of extracurricular activities

I have a third table that links these two tables as shown below:

I want to construct a query that gives me the list of all students that are participating in the same activity as a selected student. E.g. if I choose Mike, I want to have four rows:

1. Mike - Basketball - 2016
2. Lisa - Basketball - 2021
3. Mike - Football - 2018
4. John - Football - 2020

sample data

1. students

studentFirstName	studentLastName
John	Mayers
Lisa	O'Reilly
Mike	Thompson

2. activities

activityName
Basketball
Chess
Football

3. linking table

studentFirstName	studentLastName	activityStartYear
John	Chess	2017
John	Football	2020
Lisa	Basketball	2021
Lisa	Chess	2019
Mike	Basketball	2016
Mike	Football	2018

desired result

a) Input: John

Output: all students that share a common activity with John

studentFirstName	studentLastName	activityStartYear
John	Chess	2017
John	Football	2020
Lisa	Chess	2019
Mike	Football	2018

b) Input: Lisa

Output: all students that share a common activity with Lisa

studentFirstName	studentLastName	activityStartYear
John	Chess	2017
Lisa	Basketball	2021
Lisa	Chess	2019
Mike	Basketball	2016

Is there any way to do this?

CodePudding user response：

what about:

select /*b.studentFirst, */ a.* FROM third_table a
JOIN student_table b
ON a.activity = b.activity /* AND a.year = b.year -- if needed */
WHERE b.studentFirst IN ('Mike'...)

note /* */ parts kind of depends on what you really want to do

CodePudding user response：

Something like this would work:

SELECT
   name
   ,activity
   ,year
FROM linkingtable
WHERE activity IN (SELECT activity FROM linkingtable WHERE name = 'Mike')

It will return all the activities from the linkingtable based on Mike.

SQL Fiddle