CODEWITHSUNDEEP
sqljasper-reports
lamostreta
lamostreta

Reputation: 2409

Trying to Join 2 Queries in Jasper Reports

I am trying to have a table like this: 

Manager     Expert          Adminis. Staff
Dept        A.P. | C.P.     A.P. | C.P.     A.P. | C.P.
Management      1   1       1   0              1    1   
Accounting      1   1       1   1              0    0
IT              1   1       2   1              0    0

A.P. = Available Position number C.P. = Current Position number

A.P. comes from table named Positions:

[Positions](
    [id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,   
    [NumOfAvailablePositions] [int] NULL,
    [PositionType] [varchar](255) NULL, 
    [DepartmentId] [numeric](19, 0) NULL
    )

and C.P. is the count of Title_Names from Title table:

[Title](
    [id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,   
    [Title_Name] [varchar](50) NOT NULL,    
    [Department_Id] [numeric](19, 0) NULL,  
    [Position_Type] [varchar](255) NULL,
    )

In summary I am trying to have the results of these 2 queries:

SELECT   DepartmentId, PositionType, NumOfAvailablePositions  
FROM Positions 

SELECT Department_Id, Position_Type, Count (Position_Type) as CurrentPositions
FROM Title group by Department_Id, Position_Type

and join the results in one table (described above).

I tried joining the tables with the following script: 

SELECT Department_Id, PositionType, Count(Position_Type) as PositionCountTitle, Positions.DepartmentId, Positions.NumOfAvailablePositions  
FROM Title, Positions 
GROUP BY Position_Type, Department_Id, Positions.DepartmentId, Positions.NumOfAvailablePositions 
HAVING Title.Department_Id = Positions.DepartmentId

But it produced wrong results. Do you have any reccommendations to solve this problem in Jasper IReports?

Upvotes: 1

Views: 3041

Answers (2)

Jacob Schoen
Jacob Schoen

Reputation: 14202

There may be a way to do this with out using sub-queries, but I find that it is usually a good first step in getting your head around the query.

SELECT title.Department_Id,
       (SELECT NumOfAvailablePositions FROM POSITIONS WHERE Department_Id = position.Department_Id AND PositionType = 'Expert') as expert_ap,
       (SELECT COUNT(*) FROM Title WHERE Department_Id = title.Department_Id AND PositionType = 'Expert') as expert_cp,
       (SELECT NumOfAvailablePositions FROM POSITIONS WHERE Department_Id = position.Department_Id AND PositionType = 'Adminis. Staff') as admin_ap,
       (SELECT COUNT(*) FROM Title WHERE Department_Id = title.Department_Id AND PositionType = 'Adminis. Staff') as admin_cp,
       (SELECT NumOfAvailablePositions FROM POSITIONS WHERE Department_Id = position.Department_Id AND PositionType IS NULL) as rest_ap,
       (SELECT COUNT(*) FROM Title WHERE Department_Id = title.Department_Id AND PositionType IS NULL) as rest_cp,
FROM Title title, Positions position
WHERE Title.Department_Id = Positions.DepartmentId

There is an inherent problem with this query though, if the Positions table has a department that the Title table does not have, it will never be returned in the query. If this will not be the case in your case, then it is not an issue.

Upvotes: 0

Holger Brandt
Holger Brandt

Reputation: 4354

Try replacing your FROM Title, Positions with

FROM Title INNER JOIN Positions ON Title.[Position_Type]=Positions.[Position_Type]

Upvotes: 2

Related Questions