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

0

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

 

adiboing's picture
Joined: Nov 24 2010 - 5:07am
Last seen: 3 years 5 months ago

4 Answers:

0

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.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>
phantastes's picture
Joined: Aug 22 2008 - 2:05pm
Last seen: 11 months 2 weeks ago
0

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 ;)
 

adiboing's picture
Joined: Nov 24 2010 - 5:07am
Last seen: 3 years 5 months ago
0

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

cbarlow3's picture
703
Joined: Mar 4 2010 - 8:59am
Last seen: 1 week 4 days ago
0

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!

 

phantastes's picture
Joined: Aug 22 2008 - 2:05pm
Last seen: 11 months 2 weeks ago
Feedback
randomness