How can I get iReports to recognize the second set of fields in a second select statement?

[warren.perdue]

Hi Guys,

I am not sure how to phrase this but I will try my best. I am trying to union two tables within iReports and then use the fields in the report without having to use a sub-report. How can I do that? The following is an example of what I am doing but my actual has 18 columns in each table I need to access ut the example nly has 4 so I can keep it short here. How can I make this happen?

My SQL:

SELECT fund_id, subplan_id
FROM subplan
UNION  (I have also tried INTERSECT, INNER JOIN, OUTER JOIN)
SELECT employer_id, employer_name
FROM employer

Results in both MySQL and SQl are below:

fund_id         subplan_id

1F789464    46578913
79846           4679
98789789    98789789
F789654E    78946456
100000         Klingon Empire
158975        Romulan Empire
215524        Kardisan Empire
235687        Brein Federation
325658        White House
489654        Stargate COmmand

I would like for them to be independent fields; but iReports only recognizes fund_id and subplan_id and ignores the employer_id and employer_name from the second select statement. No matter how I do the SQL it doesnt work. Either I get syntax error or it just displays the list under fund_id and subplan_id.  SQL and MySQl recognize it but iReports wont accept any code other than the union.

If you all could help me out with getting this to work without having to use sub-reports it would be really great.

 

Warren

 

 

[sanbez]

You mix different fields from different tables in one SQL-query. It is not problem of iReport. It is problem to make correct query. Read documentation about basic concepts of SQL.
For example:
http://databases.about.com/od/sql/l/aajoins2.htm
http://databases.about.com/od/sql/a/multiple_joins.htm