Why each fields is displaying values thrice instead of one time in SQL Server 2014

user_3971643 0
Viewed 48

I have the following tables:

  • TB1: Student (StudentID (pk), FirstName, LastName)
  • TB2: ContactType (ContactTypeId (pk), Contact_Type)
  • TB3: Contact (ContactId (pk), StudentID (fk), ContactInfo, ContactDate, ContactTypeId (fk))

I have inserted 3 values into Contact_Type

1    mail
2    email
3    phone

and 10 values in Student table. I am trying to accomplish a task such that if the Studentid is 2 and ContactTypeID is 1, then on using join statement it should only display Studentid and should display only mail. When I try to run below JOIN statement it is displaying all 3 columns after executing below join statement.

Student table:

INSERT INTO Student(FirstName,LastName)
VALUES ('Franklin', 'Babb'), ('Frederick', 'Raub'), ('William', 'Liess'),
       ('Thomas', 'Santiago'), ('Marian', 'Draudit'),('Aurther', 'Harrington'),
       ('William', 'Sanson'), ('Bobby', 'McLain'), ('Noble', 'Hughes'),
       ('Edwin', 'Hart')

ContactType table has the following info:

  INSERT INTO ContactType(Contact_Type)
  VALUES ('Email'), ('Phone'), ('Mail')

Contact table has the following info.

INSERT INTO Contact(StudentID, ContactTypeId, ContactInfo, ContactDate)
VALUES (1, 2, 'N/A', '1 Oct 2017'),
       (2, 1, 'Student was contacted via email and phone', '5 Oct 2017'),
       (3, 3, 'N/A', '15 Oct 2017'),
       (4, 1, 'N/A', '25 Oct 2017'),
       (5, 3, 'Student was contacted via email and mail', '15 Nov 2017'),
       (6, 2, 'Student was contacted via email and phone', '15 Oct 2017'),
       (7, 3, 'Student was contacted via mail and phone', '10 Oct 2017'),
       (8, 3, 'Student was contacted via email and phone', '2 Oct 2017'),
       (9, 2, 'Student was contacted via email and phone', '5 Oct 2017'),
       (10, 2, 'N/A', '23 Oct 2017')

JOIN statement:

SELECT
    Student.StudentID,
    ContactType.Contact_Type,
    Student.LastName + ',' + FirstName AS Student_Name,
    Contact.ContactInfo, Contact.ContactDate
FROM 
    Student
JOIN 
    Contact ON Student.StudentID = Contact.StudentID
JOIN 
    ContactType ON Student.StudentID = Contact.StudentID

Any help is appreciated.

Thanks in advance!

sql-server-2014 sql-server sql
1 Answers

You are using the same JOIN ON condition for both joined tables, the conditions should be different, like this:

SELECT
...
FROM Student
JOIN Contact     ON Student.StudentID         = Contact.StudentID
JOIN ContactType ON ContactType.ContactTypeID = Contact.ContactTypeID
user_12205500
Related