In SQL I need you to take the following query and in the first line replace Full
ID: 3881502 • Letter: I
Question
In SQL I need you to take the following query and in the first line replace Fullname with subquery 1 and AlternateContactPersonID with subquery 2 and make sure the syntax is correct.
SELECT SupplierName, FullName, AlternateContactPersonID,
DeliveryAddressLine1, DeliveryAddressLine2, DeliveryCityID,
CityName, StateProvinceCode, DeliveryPostalCode
FROM
Purchasing.Suppliers PS LEFT JOIN Application.People AP
ON PS.PrimaryContactPersonID = AP.PersonID
LEFT JOIN Application.cities AC
ON AC.CityID = PS.DeliveryCityID
LEFT JOIN Application.StateProvinces Ast
ON AC.StateProvinceID = Ast.StateProvinceID
--Subquery 1 below
(SELECT FullName
FROM
Purchasing.Suppliers S INNER JOIN Application.People PC
ON S.PrimaryContactPersonID = PC.PersonID) AS PrimaryContactName
--Subquery 2 below
(SELECT FullName
FROM
Purchasing.Suppliers S INNER JOIN Application.People PC
ON S.AlternateContactPersonID = PC.PersonID) AS AlternateContactName
Explanation / Answer
You can do the following to get the Full name from subquery1 and AlternateContactPersonID from subquery2
SELECT SupplierName, q1.FullName, q2.AlternateContactPersonID,
DeliveryAddressLine1, DeliveryAddressLine2, DeliveryCityID,
CityName, StateProvinceCode, DeliveryPostalCode FROM
Purchasing.Suppliers PS LEFT JOIN Application.People AP
ON PS.PrimaryContactPersonID = AP.PersonID
LEFT JOIN Application.cities AC
ON AC.CityID = PS.DeliveryCityID
LEFT JOIN Application.StateProvinces Ast
ON AC.StateProvinceID = Ast.StateProvinceID,
(SELECT FullName FROM Purchasing.Suppliers S INNER JOIN Application.People PC
ON S.PrimaryContactPersonID = PC.PersonID) q1,
(SELECT FullNameFROM Purchasing.Suppliers S INNER JOIN Application.People PC
ON S.AlternateContactPersonID = PC.PersonID) q2
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.