Ggtcf

Why "had" in "[something] we would have made had we used [something]"?

Are Captain Marvel's powers affected by Thanos' actions in Infinity War

Store Credit Card Information in Password Manager?

Limits and Infinite Integration by Parts

Why is it that I can sometimes guess the next note?

How to hide some fields of struct in C?

Strong empirical falsification of quantum mechanics based on vacuum energy density

Lowest total scrabble score

How does a computer interpret real numbers?

Can a stoichiometric mixture of oxygen and methane exist as a liquid at standard pressure and some (low) temperature?

The probability of Bus A arriving before Bus B

Biological Blimps: Propulsion

Plot of a tornado-shaped surface

What is the English pronunciation of "pain au chocolat"?

Multiplicative persistence

How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?

Can a College of Swords bard use a Blade Flourish option on an opportunity attack provoked by their own Dissonant Whispers spell?

Open a doc from terminal, but not by its name

Temporarily disable WLAN internet access for children, but allow it for adults

What is the evidence for the "tyranny of the majority problem" in a direct democracy context?

Electoral considerations aside, what are potential benefits, for the US, of policy changes proposed by the tweet recognizing Golan annexation?

What are some good ways to treat frozen vegetables such that they behave like fresh vegetables when stir frying them?

What should you do if you miss a job interview (deliberately)?

Pre-mixing cryogenic fuels and using only one fuel tank

SQL Retrieve Distinct Records in New Table Before Joining

Add a column with a default value to an existing table in SQL ServerSQL update from one Table to another based on a ID matchHow can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?How can I do an UPDATE statement with JOIN in SQL?Retrieving the last record in each group - MySQLUpdate a table using JOIN in SQL Server?Select distinct records on a joinFinding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?How to Delete using INNER JOIN with SQL Server?

0

I have a query which I am trying to join Staff table to a new table JobInfo,

on staff.StaffID to jobinfo.staff_id.

var leaveSearchResult = 
(from staff in db.Staffs
 join leave in db.LeaveApplication
 on staff.StaffID equals leave.StaffID
 join appliedLeave in db.AppliedLeaves
 on leave.LeaveApplicationID equals appliedLeave.LeaveApplicationID
 join staffLeave in db.StaffLeaves
 on leave.StaffID equals staffLeave.StaffID
 join leaveType in db.LeaveTypes
 on staffLeave.LeaveID equals leaveType.LeaveTypeID
 where leave.LeaveStatus == LeaveBusinessLogic.AppliedLeaveStatus.Approved.ToString()

 //join JobInfo table based on staffid
 join jobinfo in db.JobInfo
 on staff.StaffID equals jobinfo.staff_id

 select new staffDetails = staff, leaveDetails = leave, appliedLeaves = appliedLeave ).Distinct().AsQueryable();

In JobInfo table, there may be more than one record for each staff_id

How can I retrieve only 1 record for each staff_id based on the highest primary key jobinfo_id in JobInfo table?

E.g.

 join jobinfo in db.JobInfo

 on staff.StaffID equals (jobinfo.staff_id).OrderByDescending(jobinfo.jobinfo_id).Distinct()
 //^currently showing error

sql join distinct

share|improve this question

asked Mar 8 at 2:37

gymcodegymcode

1,585104589

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Instead of joining on jobInfo, try to group joinInfo on staff id and take then max of jobinfo_id.
  
  – peeyush singh
  Mar 8 at 2:56
  

  
  
  
  

add a comment | 

0

I have a query which I am trying to join Staff table to a new table JobInfo,

on staff.StaffID to jobinfo.staff_id.

var leaveSearchResult = 
(from staff in db.Staffs
 join leave in db.LeaveApplication
 on staff.StaffID equals leave.StaffID
 join appliedLeave in db.AppliedLeaves
 on leave.LeaveApplicationID equals appliedLeave.LeaveApplicationID
 join staffLeave in db.StaffLeaves
 on leave.StaffID equals staffLeave.StaffID
 join leaveType in db.LeaveTypes
 on staffLeave.LeaveID equals leaveType.LeaveTypeID
 where leave.LeaveStatus == LeaveBusinessLogic.AppliedLeaveStatus.Approved.ToString()

 //join JobInfo table based on staffid
 join jobinfo in db.JobInfo
 on staff.StaffID equals jobinfo.staff_id

 select new staffDetails = staff, leaveDetails = leave, appliedLeaves = appliedLeave ).Distinct().AsQueryable();

In JobInfo table, there may be more than one record for each staff_id

How can I retrieve only 1 record for each staff_id based on the highest primary key jobinfo_id in JobInfo table?

E.g.

 join jobinfo in db.JobInfo

 on staff.StaffID equals (jobinfo.staff_id).OrderByDescending(jobinfo.jobinfo_id).Distinct()
 //^currently showing error

sql join distinct

share|improve this question

asked Mar 8 at 2:37

gymcodegymcode

1,585104589

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Instead of joining on jobInfo, try to group joinInfo on staff id and take then max of jobinfo_id.
  
  – peeyush singh
  Mar 8 at 2:56
  

  
  
  
  

add a comment | 

I have a query which I am trying to join Staff table to a new table JobInfo,

on staff.StaffID to jobinfo.staff_id.

var leaveSearchResult = 
(from staff in db.Staffs
 join leave in db.LeaveApplication
 on staff.StaffID equals leave.StaffID
 join appliedLeave in db.AppliedLeaves
 on leave.LeaveApplicationID equals appliedLeave.LeaveApplicationID
 join staffLeave in db.StaffLeaves
 on leave.StaffID equals staffLeave.StaffID
 join leaveType in db.LeaveTypes
 on staffLeave.LeaveID equals leaveType.LeaveTypeID
 where leave.LeaveStatus == LeaveBusinessLogic.AppliedLeaveStatus.Approved.ToString()

 //join JobInfo table based on staffid
 join jobinfo in db.JobInfo
 on staff.StaffID equals jobinfo.staff_id

 select new staffDetails = staff, leaveDetails = leave, appliedLeaves = appliedLeave ).Distinct().AsQueryable();

In JobInfo table, there may be more than one record for each staff_id

How can I retrieve only 1 record for each staff_id based on the highest primary key jobinfo_id in JobInfo table?

E.g.

 join jobinfo in db.JobInfo

 on staff.StaffID equals (jobinfo.staff_id).OrderByDescending(jobinfo.jobinfo_id).Distinct()
 //^currently showing error

sql join distinct

share|improve this question

asked Mar 8 at 2:37

gymcodegymcode

1,585104589

var leaveSearchResult = 
(from staff in db.Staffs
 join leave in db.LeaveApplication
 on staff.StaffID equals leave.StaffID
 join appliedLeave in db.AppliedLeaves
 on leave.LeaveApplicationID equals appliedLeave.LeaveApplicationID
 join staffLeave in db.StaffLeaves
 on leave.StaffID equals staffLeave.StaffID
 join leaveType in db.LeaveTypes
 on staffLeave.LeaveID equals leaveType.LeaveTypeID
 where leave.LeaveStatus == LeaveBusinessLogic.AppliedLeaveStatus.Approved.ToString()

 //join JobInfo table based on staffid
 join jobinfo in db.JobInfo
 on staff.StaffID equals jobinfo.staff_id

 select new staffDetails = staff, leaveDetails = leave, appliedLeaves = appliedLeave ).Distinct().AsQueryable();

 join jobinfo in db.JobInfo

 on staff.StaffID equals (jobinfo.staff_id).OrderByDescending(jobinfo.jobinfo_id).Distinct()
 //^currently showing error

share|improve this question

asked Mar 8 at 2:37

gymcodegymcode

1,585104589

share|improve this question

asked Mar 8 at 2:37

gymcodegymcode

1,585104589

asked Mar 8 at 2:37

gymcodegymcode

1,585104589

asked Mar 8 at 2:37

gymcodegymcode

1,585104589