its my first time I work with iReport and I have a problem which seems a bit confusing. To get the data out of my database I wrote in the "Report Query" -Window the following query:
SELECT ATTACHEDFILE, NID, FILETYPE FROM GekkoATTACH WHERE NID >= 970 ORDER BY NID ASC
In ATTACHEDFILE is a Hex-Code for the different files. And the NID is just the id. FILETYPE shows if it is a "pdf" or "jpg". And as you can see I just like to show all files after the ID 970.
And now I would like to insert the author who attached the file to the database. But the entry for the author is in another table. So my Query should look like this.
SELECT AUTHOR FROM GekkoNOTE WHERE NID >= 970 ORDER BY NID ASC
But I realy don't know how to get the author from another table to insert it via the fields to my report.
I hope you could understand my problem, if not ask again pls.
I would recommend reading through one of the many SQL tutorials online. Just google "sql tutorial".
In the case you mention you would need to join the two tables. Without knowing how the two table look like I cannot guarantee that this is the right solution, but if NID is the key it would look something like this:
SELECT ga.attachedfile, ga.nid, ga.filetype, gn.author FROM GekkoATTACH ga INNER JOIN GekkoNOTE gn ON ga.nid = gn.nid WHERE ga.nid >= 970 ORDER BY ga.nid ASC</td></tr></tbody></table>
You could have multiple SQL queries by using a List component or by using SubReports...but you wouldn't get the effect that you're probably after of relating information from one table with information from another table. The standard way of doing that with a relational database is with the various types of JOIN, regardless of what reporting tool you use. It's not a shortcoming of iReport: it's a matter of iReport using the same technique that every other SQL-based tool uses for recombining related information that has been split into separate tables for normalization reasons.