Click here to Skip to main content
14,732,788 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have biodata table which have morethan 20 rows but here it showing only 4 row records ??

What I have tried:

SELECT  DISTINCT    
  d1.emp_code,
  d1.emp_name, 
  dbo.Setup_designation.des_name, 
  dbo.Setup_Department.Depart_name, 
  dbo.Transaction_Employee.joining_date, 
  dbo.Employee_Salary_Increment.current_salary, 
  d3.type, 
  d3.amount, 
  d2.r_casual_leave, 
  d2.r_sick_leave, 
  d2.r_annual_leave, 
  d2.r_extra_leave, 
  d2.others_leave
FROM         
  dbo.Employee_Biodata d1 
  INNER JOIN dbo.Transaction_Employee ON d1.Emp_bio_id = dbo.Transaction_Employee.Emp_bio_id 
  INNER JOIN dbo.Setup_designation ON dbo.Transaction_Employee.des_id = dbo.Setup_designation.des_id 
  INNER JOIN dbo.Setup_Department ON dbo.Transaction_Employee.Department_id = dbo.Setup_Department.Department_id 
  INNER JOIN dbo.Employee_Salary_Increment ON d1.Emp_bio_id = dbo.Employee_Salary_Increment.Emp_bio_id 
  INNER JOIN dbo.Loan_Issue d3 ON d1.Emp_bio_id = d3.Emp_bio_id 
  INNER JOIN dbo.Leave_issue d2 ON d1.Emp_bio_id = d2.Emp_bio_id where d1.Emp_bio_id = d3.Emp_bio_id and d1.Emp_bio_id = d2.Emp_bio_id 
Posted
Updated 2-Apr-20 0:35am
v3
Comments
RickZeeland 2-Aug-17 3:21am
   
We can only guess if we do not have the data, please add example data e.g. a script with CREATE TABLE, INSERT INTO statements.
Or even better: create an online example on http://sqlfiddle.com/
Ali Khan 2-Aug-17 5:10am
   
ok

1 solution

Without your data I'll need to guess.

You're using INNER JOIN - which means no record returns if it fails to find a match. It is very common, therefore, to return few than all of your rows - especially with so many joins, each having the potential to eliminate some rows.

You should use LEFT JOIN or RIGHT JOIN in appropriate locations, instead, which will return a NULL for unmatched records - but will return all of them. Use ISNULL() if you wish to have a value other than NULL for non-matches.

Now, particularly without any tables to view, I couldn't suggest which joins should be changed and to what their ON qualifier should be to make the proper match. It is important you link the table by the correct fields.

The above is based upon your Question, not your Subject Line.

   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900