iReport two tables but I don\'t know how to get all fields I need

[adiboing]

Hello guys,

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.

peace Adiboing

 

[phantastes]

Hi,

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:

Code:

SELECT     ga.attachedfile,     ga.nid,     ga.filetype,     gn.authorFROM     GekkoATTACH gaINNER JOIN GekkoNOTE gn     ON ga.nid = gn.nidWHERE     ga.nid >= 970ORDER BY     ga.nid ASC

[adiboing]

It works :D

I tried the inner join command before but not like your code.

But serious why the hell isn't it possible to include several tables in iReport via a few SELECT commands. In my opinion thats a huge gap in the programm.

thanks to you, Adi ;)
 

[cbarlow3]

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.

Carl

[phantastes]

Hi Adi,

Like I mentioned before:

"I would recommend reading through one of the many SQL tutorials online. Just google "sql tutorial".

Your question is related to basic SQL syntax and has nothing to do with iReport.

Best of luck!