Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
SolvedPrivate

Select multiple tables in LINQ

Posted on 2014-09-04
5
Medium Priority
?
45 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

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

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

719 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