question

Gehima2016 avatar image
Gehima2016 asked

Join Syntax

Hi, Can some check this join syntax and see if its correct; CREATE TABLE Emp ( EmpID int, EmpName varchar(50), DeptID int, StartDate datetime, Salary money, LocationID int, EmpStatus int ) CREATE TABLE Location ( LocationID int, Adrress1 varchar(50), Address2 varchar(50), City varchar(50), State VARCHAR(50) ) CREATE TABLE Dept ( DeptID int, DeptName varchar(50) ) -- Write a Tsql query joining all three tables emp e, dept d, location l --INNER JOIN SELECT e.EmpID, e.EmpName, e.DeptID, e.LocationID, d.DeptName, l.City, l.State FROM Emp e INNER JOIN Dept d ON d.DeptID = e.DeptID INNER JOIN Location l ON e.LocationID = l.LocationID --left outer join SELECT e.[EmpID], e.[EmpName], e.[Salary], e.DeptID, d.DeptName FROM Dept d LEFT OUTER JOIN Emp e ON e.DeptID = d.DeptID --Right Outer Join SELECT e.EmpID, e.EmpName, d.DeptID, d.DeptName FROM Dept d RIGHT OUTER JOIN Emp e ON d.DeptID = E.DeptID CREATE TABLE Stg_Emp ( EmpID int, Empname varchar(50) ) INSERT INTO Stg_Emp(EmpID, Empname) VALUES (1, 'John Doe'), (2, 'Jane Doe'), (3, 'Sally Mae') CREATE TABLE Emp_List ( EmpID int, EmpName varchar(50) ) INSERT INTO Emp_List (EmpID, EmpName) VALUES (1, 'John Doe'), (2, 'Jane Doe'), (5, 'Peggy Sue') --1) The stg_Emp table contains all new and current employees --2) The Emp_List tablecontains some current and all former employee Stg_Emp = s, Emp_List = e --1) Show company new employee SELECT s.EmpID, s.EmpName, e.EmpName FROM Stg_Emp s LEFT OUTER JOIN Emp_List e ON s.EmpID=e.EmpID --2) Show company former employee SELECT s.EmpID, s.EmpName, e.EmpName FROM Stg_Emp s RIGHT OUTER JOIN Emp_List e ON s.EmpID=e.EmpID
joinsyntax
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Avi1 avatar image
Avi1 answered
Exists is better choice as compare to LEFT JOIN --1) Show company new employee SELECT s.EmpID, s.EmpName ---, e.EmpName, need to remove this, since this data is not available in Emp_List FROM Stg_Emp s WHERE NOT EXISTS (SELECT * FROM Emp_List e where s.EmpID=e.EmpID) -->this will ignore current employees --2) Show company former employee SELECT e.EmpID, e.EmpName --s.EmpID, s.EmpName : need to remove these, since this data is not available in Stg_Emp FROM Emp_List e WHERE NOT EXISTS (SELECT * FROM Stg_Emp s where s.EmpID=e.EmpID) -->this will ignore current employees
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.