1) FOR EVERY CUSTOMER WITH 'MAIN OFFICE' IN DALLAS SHOW THE ADDRESS LINE 1 OF THE -- 'MAIN OFFICE' AND ADDRESSLINE 1 OF THE 'SHIPPNG'ADRESS - IF THERE IS -- NO SHIPPING ADDRESS LEAVE IT BLANK .USE ONE ROW PER CUSTOMER - USING ADVENTURE WORKS I VE WRITTEN AS SELECT * FROM (SELECT A.City,D.Name AS COMPANYNAME,A.AddressLine1 AS MAINOFFICE ,C.AddressTypeID FROM Person.Address A JOIN Person.BusinessEntityAddress B ON (A.AddressID=B.AddressID) JOIN Person.AddressType C ON (C.AddressTypeID=B.AddressTypeID) JOIN Sales.Store D ON (D.BusinessEntityID =B.BusinessEntityID) WHERE C.Name = 'main office') as g1 FULL JOIN (SELECT A1.City,D1.Name AS COMPANYNAME,F1.AddressLine1 as [shipping addr],C1.AddressTypeID FROM Person.Address A1 JOIN Person.Address F1 ON (A1.AddressID=F1.AddressID) JOIN Person.BusinessEntityAddress B1 ON (A1.AddressID=B1.AddressID) JOIN Person.AddressType C1 ON (C1.AddressTypeID=B1.AddressTypeID) JOIN Sales.Store D1 ON (D1.BusinessEntityID=B1.BusinessEntityID) WHERE C1.Name ='Shipping' ) AS G2 ON G1.AddressTypeID=G2.AddressTypeID WHERE G1.City = 'DALLAS' OR G2.City ='DALLAS'
This sounds like a homework question, but I don't mind helping since it looks like you have already tried yourself. First of all, I'd like to point out that subqueries within the SELECT of a main query typically perform very poorly because you are essentially asking the engine to run the subquery for every row of the main query. Whenever possible, it's better to restructure to run a single set operation. Also, in your answer, checking if the G1 city or G2 city is "Dallas" returns every store where either the main office or the shipping address is in Dallas. I believe the following query will give you what you want. DECLARE @ShippingAddressTypeId int = (SELECT t.AddressTypeID FROM Person.AddressType t WHERE t.Name = 'Shipping'); SELECT MainOfficeAddressLine1 = swa.AddressLine1 , ShippingAddressLine1 = sa.AddressLine1 FROM Sales.vStoreWithAddresses swa LEFT JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = swa.BusinessEntityID AND bea.AddressTypeID = @ShippingAddressTypeId LEFT JOIN Person.Address sa ON sa.AddressID = bea.AddressID WHERE swa.AddressType = 'Main Office' AND swa.City = 'Dallas' ;