CODEWITHSUNDEEP
sqlsql-server-2008joinouter-join
user1157084
user1157084

Reputation: 1

SQL Distinctive Join

I'm trying to join a distinct field on another field.

Example: There are multiple rows in tbl.Class classified under column.Teacher that have the same value. I want to join just one DISTINCT value from column.Teacher to column.Teacher_Course in another table (tbl.Course).

Here is what I am thinking, but instead of only retrieving that one value from tbl.Class, I get multiple joins.

SELECT distinct(Teacher.JohnJoe)
FROM  tbl.Class

RIGHT OUTER JOIN tbl.Course ON Course.Teacher_Course = Class.Teacher

Upvotes: 0

Views: 114

Answers (2)

Jacco
Jacco

Reputation: 3272

Resume:

  • multiple teachers in tbl.Class
  • single teacher in tbl.Course
  • the result should give a single teacher from tbl.Class for each record in tbl.Course

My SQL interpretation:

DECLARE @class TABLE ([id] int, [teacher] nvarchar(20))
DECLARE @course TABLE ([id] int, [course] nvarchar(20), [teacher_course] nvarchar(20))

INSERT INTO @class ([id], [teacher]) VALUES (1, 'Teacher 1')
INSERT INTO @class ([id], [teacher]) VALUES (2, 'Teacher 2')
INSERT INTO @class ([id], [teacher]) VALUES (3, 'Teacher 3')
INSERT INTO @class ([id], [teacher]) VALUES (4, 'Teacher 4')
INSERT INTO @class ([id], [teacher]) VALUES (5, 'Teacher 1')

INSERT INTO @course ([id], [course], [teacher_course]) VALUES (1, 'Course 1', 'Teacher 1')
INSERT INTO @course ([id], [course], [teacher_course]) VALUES (2, 'Course 2', 'Teacher 4')

SELECT 
    [co].[id],
    [co].[course],
    [cl].[teacher]
FROM  @course [co]
INNER JOIN @class [cl] ON [co].[teacher_course] = [cl].[teacher]
GROUP BY
    [co].[id],
    [co].[course],
    [cl].[teacher]

Which results in:

[id]  [course]  [teacher]
1     Course 1  Teacher 1
2     Course 2  Teacher 4

Upvotes: 0

Gustavo Gondim
Gustavo Gondim

Reputation: 1643

You may also try to use the GROUP BY clause:

SELECT Teacher.JohnJoe
FROM  tbl.Class
RIGHT OUTER JOIN tbl.Course ON Course.Teacher_Course = Class.Teacher  
GROUP BY Teacher.JohnJoe

Upvotes: 1

Related Questions