Displaying 2 related records from the same table on the same Jasper form

[i.vaughan]

Hi

How using ireport designer can I include 2 related records from the same table to output on one report.   I have a table that contains marriage data so in one row will be the groom data and the other the bride data (field names are the same).  

I have got one record displaying on the jasper report using WHERE ID = $P{marriageid} 

But how would I adapt this and the report to display both the bride and the groom details on the same report. The where clause would be  marriagedate, bookid,booknumber

I would also need to have 2 lots of each text fields displayed on the form because the positioning of the output data for the groom is in a different position on the form to the bride.

 

Hope someone can shed some light on this and the best solution please

[james.t]

This sounds like you just need to write your SQL query in such a way that it brings back details of bride and groom in a single row.

So you might have something like:

 

SELECT

B.NAME AS BRIDE_NAME

,G.NAME AS GROOM_NAME

FROM

MARRIAGE_DATA B

JOIN MARRIAGE_DATA G ON G.MARRIAGEID = B.MARRIAGEID

WHERE

B.TYPE = 'Bride'

AND G.TYPE = 'Groom';