Select a record with highest amount by joining two tables
- by user2516394
I've 2 tables Sales & Purchase, Sales table with fields SaleId, Rate, Quantity, Date, CompanyId, UserID. Purchase table with fields PurchaseId, Rate, Quantity, Date, CompanyId, UserID.
I want to select a record from either table that have highest Rate*Quantity.
SELECT SalesId Or PurchaseId FROM Sales,Purchase
where Sales.UserId=Purchase.UserId and Sales.CompanyId=Purchase.CompanyId
AND Sales.Date=Current date
AND Purchase.Date=Current date AND Sales.UserId=1
AND Purchase.UserId=1 AND Sales.CompanyId=1 AND Purchase.ComoanyId=1
