CODEWITHSUNDEEP
sqlsql-serversubquery
j_t_fusion
j_t_fusion

Reputation: 223

select rows based on WHERE clause which return multiple rows

 Mentor table
+--------------+
| id  |  name  |
+-----+--------+
|  1  | name1  |
|  2  | name2  |
|  3  | name3  |
+-----+--------+

 MentorLanguage table
+------------------+
| id  |  language  |
+-----+------------+
|  1  |   english  |
|  1  |   french   |
|  1  |   german   |
|  2  |   chinese  |
|  2  |   english  |
|  3  |   russian  |
|  3  |   german   |
|  3  |   greek    |
+-----+------------+

 Student table
+--------------+
| id  |  name  |
+-----+--------+
|  A  | name1  |
|  B  | name2  |
|  C  | name3  |
+-----+--------+

 StudentLanguage table
+------------------+
| id  |  language  |
+-----+------------+
|  A  |   english  |
|  A  |   french   |
|  B  |   chinese  |
|  B  |   german   |
|  C  |   russian  |
|  C  |   spanish  |
|  C  |   greek    |
+-----+------------+


I want to match mentor with student based on the language, such that for example:

if student A knows english and french, he will be matched with all mentors that know at least english or french.

student A (english, french)
---------------------------------
mentor 1 (english, french, german); 
mentor 2 (chinese, english);


I tried

select * from Mentor m
where m.id =
( select ml.id from MentorLanguage ml, StudentLanguage sl
  where ml.language like sl.language 
  group by ml.id )

which doesn't work since the Subquery returned more than 1 value.

Upvotes: 0

Views: 1205

Answers (3)

dipal
dipal

Reputation: 71

Did you expect like this ??

select 
Student.id StudentId,
group_concat(distinct StudentLanguage.language) Languages,
group_concat(distinct Mentor.id) MentorIds from 
Student join StudentLanguage on (Student.id = StudentLanguage.id) 
join MentorLanguage on (StudentLanguage.language = MentorLanguage.language) 
join Mentor on (MentorLanguage.id = Mentor.id) group by Student.id;

which results:

+-----------+----------------+-----------+
| StudentId | Languages      | MentorIds |
+-----------+----------------+-----------+
| A         | french,english | 1,2       |
| B         | german,chinese | 1,3,2     |
| C         | russian,greek  | 3         |
+-----------+----------------+-----------+

For the last row, C know spanish but no mentors knows it. If you need spanish also in the list use left join.

Upvotes: 0

DLeh
DLeh

Reputation: 24385

You could try using the "IN" operator instead of = in your where clause. This allows you to to do a "contains" instead of comparing with a single value.

select * from Mentor m
where m.id IN
( select ml.id from MentorLanguage ml, StudentLanguage sl
  where ml.language like sl.language 
  group by ml.id )

Upvotes: 1

Nick H.
Nick H.

Reputation: 1616

There are a ton of ways to do this. I guess it just depends on your preference and/or need in the result set. I've included two ways I would meet this request. Pretty simple. Let me know if you need additional returned results. 

CREATE TABLE #Mentor ([id] INT Identity, [name] NVARCHAR(20))
GO

INSERT INTO #Mentor(name)
VALUES ('John Smith'),('Jack Smith'),('Jane Smith')

CREATE TABLE #MentorLanguage ([id] INT, [language] NVARCHAR(20))
GO

INSERT INTO #MentorLanguage([id],[language])
VALUES (1,'English'),(1,'French'),(1,'German')
      ,(2,'Chinese'),(2,'English'),(3,'Russian')
      ,(3,'German'),(3,'Greek')

CREATE TABLE #Student([id] NVARCHAR(2), [name] NVARCHAR(20))
GO

INSERT INTO #Student ([id],[name])
VALUES ('A','name1'),('B','name2'),('C','name3')

CREATE TABLE #StudentLanguage ([id] NVARCHAR(2),[language] NVARCHAR(20))
GO

INSERT INTO #StudentLanguage ([id],[language])
VALUES ('A','English'),('A','French'),('B','Chinese'),('B','German'),('C','Greek')

/* Inner Join to between #MentorLanguage and #StudentLanguage 
    would elimate rows where the mentor and student don't match */
SELECT * 
FROM #Mentor m
INNER JOIN #MentorLanguage ml ON m.[id] = ml.id
INNER JOIN #StudentLanguage sl ON ml.[language] = sl.[language]
INNER JOIN #Student s ON s.id = sl.id

/* Agg Count of how many students each mentor could teach
   based on the languages students know */

SELECT m.name, count(s.id) as [count]
FROM #Mentor m
INNER JOIN #MentorLanguage ml ON m.[id] = ml.id
INNER JOIN #StudentLanguage sl ON ml.[language] = sl.[language]
INNER JOIN #Student s ON s.id = sl.id
GROUP BY m.name

Upvotes: 0

Related Questions