MS SQL Join View Table. HELP

[FooKelvin]

Hello,

 

I have a question regarding SQL. Please refer my attachment below:

 

Query 1:

SELECT     dbo.[evaluation[Submit]]].FormID, dbo.empDB.Name, dbo.FormTbl.FormName, dbo.[evaluation[Submit]]].groupName, dbo.[evaluation[Submit]]].subGroup, 

                      dbo.subgrouptbl.subgroupName

FROM         dbo.[evaluation[Submit]]] INNER JOIN

                      dbo.empDB ON dbo.[evaluation[Submit]]].EmpID = dbo.empDB.EmployeeID INNER JOIN

                      dbo.FormTbl ON dbo.[evaluation[Submit]]].FormID = dbo.FormTbl.FormID INNER JOIN

                      dbo.subgrouptbl ON dbo.[evaluation[Submit]]].subGroup = dbo.subgrouptbl.subgroupID

WHERE     (dbo.[evaluation[Submit]]].EmpID = '00001')

This view of table give me correct of the output. Which give me 36 of results. Please refer attachment below:

 

But, when i try to put additional table which i need to indicated whether the form is completed or draft, it's because i do not want to show "draft" form in the table.

 

Have a look on the table below:

 

After i added this table into my view, the output is repeated 2 times and give me 72 of results. 

Query 2:

SELECT     dbo.[evaluation[Submit]]].FormID, dbo.empDB.Name, dbo.FormTbl.FormName, dbo.[evaluation[Submit]]].groupName, dbo.[evaluation[Submit]]].subGroup, 

                      dbo.subgrouptbl.subgroupName, dbo.EmployeeDetails.submissionStatus

FROM         dbo.[evaluation[Submit]]] INNER JOIN

                      dbo.empDB ON dbo.[evaluation[Submit]]].EmpID = dbo.empDB.EmployeeID INNER JOIN

                      dbo.FormTbl ON dbo.[evaluation[Submit]]].FormID = dbo.FormTbl.FormID INNER JOIN

                      dbo.subgrouptbl ON dbo.[evaluation[Submit]]].subGroup = dbo.subgrouptbl.subgroupID INNER JOIN

                      dbo.EmployeeDetails ON dbo.empDB.EmployeeID = dbo.EmployeeDetails.EmpID

WHERE     (dbo.[evaluation[Submit]]].EmpID = '00001')

[Barand]

Do you have more than one record for each employee in the EmployeeDetails table. You probably need to join on empid and formid

[FooKelvin]

Yes Barand. I have more than one record for each employee in EmployeeDetails table. I try to join like this and still give me repeated output.

 

 

[FooKelvin]

Hi Barand, i think i fixed my problem. I overlook, actually the form and employee id will repeat at each table. The only thing that unique is the subgroup. so i link it, i think the output is correct.

 

Thank You Barand.

Have a nice day! 

 

btw, how to close topic?