'Query - select multiple rows based on condition

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:

third table

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
  1. activities
activityName
Basketball
Chess
Football
  1. 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?

sql


Solution 1:[1]

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

Solution 2:[2]

what about:

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

notes:

  • /* */ parts kind of depends on what you really want to do
  • as you see activity table is not needed but you could add a join to ensure it exists JOIN activities c ON a.activity = c.activity but this is only needed if you need to check the existence of the activity

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Christian
Solution 2

Related Questions