alexcryout
asked on
Select multiple tables in LINQ
Hi Experts,
I have this SQL query statement:
SELECT d1.*
FROM FinanceDept d1
INNER JOIN
(SELECT User_ID, MAX(SalesAmt) AS 'Amount' FROM SalesDept GROUP BY User_ID) d2
ON d1.User_ID = d2.User_ID AND d1.Amount= d2.Amount
But I don't know how to write it in LINQ and Lambda expression.
Please help.
Thank you
I have this SQL query statement:
SELECT d1.*
FROM FinanceDept d1
INNER JOIN
(SELECT User_ID, MAX(SalesAmt) AS 'Amount' FROM SalesDept GROUP BY User_ID) d2
ON d1.User_ID = d2.User_ID AND d1.Amount= d2.Amount
But I don't know how to write it in LINQ and Lambda expression.
Please help.
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi alexcryout;
Just substitute the table name SalesDept with FinanceDept as shown in this line of the query and that should do it.
(SELECT User_ID, MAX(SalesAmt) AS 'Amount' FROM FinanceDept GROUP BY User_ID) d2
Just substitute the table name SalesDept with FinanceDept as shown in this line of the query and that should do it.
(SELECT User_ID, MAX(SalesAmt) AS 'Amount' FROM FinanceDept GROUP BY User_ID) d2
ASKER
It works fine but I download the Linqer tool and its solution is much better.
Thank you for all your helps.
Thank you for all your helps.
ASKER
Sorry for my typo, I am trying to join the same table. The correct SQL statement is:
SELECT d1.*
FROM FinanceDept d1
INNER JOIN
(SELECT User_ID, MAX(SalesAmt) AS 'Amount' FROM FinanceDept GROUP BY User_ID) d2
ON d1.User_ID = d2.User_ID AND d1.Amount= d2.Amount
Thanks