SolvedPrivate

Select multiple tables in LINQ

Posted on 2014-09-04
5
36 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 250 total points
ID: 40305509
0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 250 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 62

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 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