inner join operation for fetching data from multiple tables is not working fine in Jaspersoft studio

[email.vinu04]

The query which I have written for fetching data from 3 tables is working fine in SQL server management studio and MS Access but in Jaspersoft studio it is giving unexpected output with many duplicate records. My query looks something like this

Employee table:  emp_Id, emp_name, salary, Dep_num, project_id  --->Primary key=emp_Id

Department table: Dep_num, Dep_name, Manager                     ----->Primary key= Dep_num

Project table: project_id, project_name, project_loc            ------->Primary key=project_id

I want to have a resul table like  emp_name, Dep_name,project_name

I have written query like this

select emp_name, Dep_name, project_name

from Employee as E  inner join Department as D on E.Dep_num = D.Dep_num

inner join Project as P on E.project_id = P.project.id;

so I am awaiting any response from jasper team, query is fine but jasper is not giving correct output, anyone help me out, how to deal with this issue

 

 

[reportdev]

did you create any groups on the report ?

I would suggest start from scratch and just pull all your fields into the detail band after sql evaluation.

 

[email.vinu04]

I am not using any groups or tables, because it gives duplicate records, so only I am just pulling all fields into detail band, but the problem is, it is not supporting that query well, even though it is correct

 

[jbarger.consultant]

Im getting same situation. The Inner Join doesn't appear to work correctly. It joins but joins everything on everything.