SQL JOIN with WHERE condition when two rows’ values are the same and one row matches to two different rows

I have these tables:

CREATE TABLE students(
  id int NOT NULL PRIMARY KEY,
  name VARCHAR(30) NOT NULL
 );

CREATE TABLE studentsActivities(
  studentId int NOT NULL,
  activity VARCHAR(30) NOT NULL,
  PRIMARY KEY (studentId, activity),
  foreign KEY (studentId) REFERENCES students(id) 
);

And I have to return all student names that do either Tennis or Football. However, there was a test case which I could not pass and it was stated like that:

Students with the same name.

I do not know the exact implementation of the test cases, but I suspect it was the situation where student A named Carl does Tennis and student B also named Carl does Football and Carl is showed two times. How could I query that database to get the result like that? I’ve created the demo base to try:

CREATE TABLE students(
  id int NOT NULL PRIMARY KEY,
  name VARCHAR(30) NOT NULL
 );

CREATE TABLE studentsActivities(
  studentId int NOT NULL,
  activity VARCHAR(30) NOT NULL,
  PRIMARY KEY (studentId, activity),
  foreign KEY (studentId) REFERENCES students(id) 
);

INSERT INTO students 
VALUES
(1, "Jeremy"),
(2, "Hannah"),
(3, "Luke"),
(4, "Frank"),
(5, "Sue"),
(6, "Sue"),
(7, "Peter");

INSERT INTO studentsActivities
VALUES
(1, "Tennis"),
(1, "Football"),
(2, "Running"),
(3, "Tennis"),
(4, "Football"),
(5, "Football"),
(6, "Tennis");

SQL Fiddle And let’s suppose that the passing set would be:

Jeremy 
Luke 
Frank 
Sue 
Sue

I’ve attempted with these two queries, but none gives the correct answer.

--- 1
SELECT s.name
FROM students s
JOIN studentsActivities sa
ON sa.studentId = s.id
WHERE activity = "Tennis"
UNION
SELECT s.name
FROM students s
JOIN studentsActivities sa
ON sa.studentId = s.id
WHERE activity = "Football"
--- Returns Frank Jeremy Luke Sue (missing one Sue)

--- 2
SELECT s.name
FROM students s
JOIN studentsActivities sa
ON sa.studentId = s.id
WHERE activity = "Tennis"
OR activity = "Football"
ORDER BY s.name;
--- Returns Frank Jeremy Jeremy Luke Sue Sue (too much Jeremies)

Answer

Join the tables, filter only the rows with the activities that you want and return distinct rows:

select distinct s.id, s.name
from students s inner join studentsActivities a
on a.studentId = s.id
where a.activity in ('Tennis', 'Football')

See the demo.

Results:

| id  | name   |
| --- | ------ |
| 1   | Jeremy |
| 3   | Luke   |
| 4   | Frank  |
| 5   | Sue    |
| 6   | Sue    |

If you want only the names of the students without the ids:

select s.name
from students s inner join studentsActivities a
on a.studentId = s.id
where a.activity in ('Tennis', 'Football')
group by s.id, s.name

See the demo.

Results:

| name   |
| ------ |
| Jeremy |
| Luke   |
| Frank  |
| Sue    |
| Sue    |

Advertisement

Answer