Go Premium for a chance to win a PS4. Enter to Win

x
?
SolvedPrivate

Select multiple tables in LINQ

Posted on 2014-09-04
5
Medium Priority
?
51 Views
Last Modified: 2016-02-15
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
0
Comment
Question by:alexcryout
  • 2
  • 2
5 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 1000 total points
ID: 40305509
0
 
LVL 64

Assisted Solution

by:Fernando Soto
Fernando Soto earned 1000 total points
ID: 40305938
Hi alexcryout;

Try this query and see if it returns the required results.

var results = from d1 in FinanceDept
              join d2 in SalesDept on new { d1.User_ID, d1.Amount  } equals new { d.OrderID, d2.Amount } into userGroup
              orderby d1.User_ID
              select new 
              {
                 FinanceDept = d1,
                 User_ID_d2 = userGroup.Select( u => u.User_ID ).FirstOrDefault(),
                 Amount = userGroup.Max( a = a.SalesAmt )
              };

Open in new window

0
 

Author Comment

by:alexcryout
ID: 40306230
Hi Fernando,

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
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 40306311
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
0
 

Author Closing Comment

by:alexcryout
ID: 40306382
It works fine but I download the Linqer tool and its solution is much better.

Thank you for all your helps.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question