[Webinar] Streamline your web hosting managementRegister Today

x
?
SolvedPrivate

Select multiple tables in LINQ

Posted on 2014-09-04
5
Medium Priority
?
56 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 71

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

591 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