I am curious to know about the differences between using group by statement and nested selects to achieve the same result. for example consider I have two tables such as the following:
tblPurchase
ID
SellerID
BuyerID
Date
Price
tblPerson
ID
Name
Mail
Mobile
Now i want to show the list of purchases along with the associated names of the seller and the buyer Since sellerID and BuyerID refers to the same table, i need two nested selects to fetch their names, so i would write:
Select tblPurchase.ID, tblPurchase.Date,
select (tblPerson.Name from tblPerson where tblPerson.ID = tblPurchase.SellerID ) as [Seller's Name],
select (tblPerson.Name from tblPerson where tblPerson.ID = tblPurchase.BuyerID ) as [Buyer's Name]
from tblPurchase
this is the first method, and i guess it can be done using group by statement as well ( though myself couldn't do it, since both fields refer to one able, i got stuck at the join section!)
Is there any difference between these two statements ? since i think group by itself is actually translated to the same nested selects later and then executed.