SIMULATION
You have a database that contains the following tables.
You need to create a query that returns each complaint, the names of the employees handling the complaint,
and the notes on each interaction. The Complaint field must be displayed first, followed by the employee’s
name and the notes. Complaints must be returned even if no interaction has occurred.
Construct the query using the following guidelines:
Use two-part column names.
Use one-part table names.
Use the first letter of the table name as its alias.
Do not Transact-SQL functions.
Do not use implicit joins.
Do not surround object names with square brackets.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer
area that resolves the problem and meets the stated goals or requirements. You can add code within the code
that has been provided as well as below it.
1 SELECT c.Complaint, e.Name, i.Notes
2 FROM Complaints c
3 JOIN __________________
4 JOIN __________________
Answer: See the explanation
Explanation:
1 SELECT c.Complaint, e.Name, i.Notes
2 FROM Complaints c
3 JOIN Interactions i ON c.ComplaintID = i.ComplaintID
4 JOIN Employees e ON i.EmployeeID = E.EmployeeID
it s a tricky one the case says Complaints must be returned even if no interaction has occurred so it sounds like a left join is needed somewhere…
The answer is wrong!
If you just write “JOIN” it is an inner join. Hence Complaints without interaction are filtered out.
Correct statment would be
SELECT c.Complaint, e.Name, i.Notes
FROM Complaints c
LEFT OUTER JOIN Interactions i ON c.ComplaintID = i.ComplaintID
LEFT OUTER JOIN Employees e ON i.EmployeeID = e.EmployeeID
https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join
Because not INNER JOIN?
SELECT c.Complaint, e.Name, i.Notes
FROM Complaints c
INNER JOIN Interactions i ON c.ComplaintID = i.ComplaintID
INNER JOIN Employees e ON i.EmployeeID = e.EmployeeID
that is exactly correct Peter and you need 2 Left joins for that
I have latest dumps [email protected] for 70-761, 70-762, 70-764, 70-767
SELECT c.Complaint, e.Name, i.Notes
FROM Complaints c
LEFT OUTER JOIN Interactions i ON c.ComplaintID = i.ComplaintID
LEFT OUTER JOIN Employees e ON i.EmployeeID = e.EmployeeID