question

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' ;