CODEWITHSUNDEEP
javasqljasper-reports
zairahCS
zairahCS

Reputation: 325

How to query when joining 3 queries into 1 table?

I have 3 queries:

// (1)
String sql = "SELECT tblClientInfo.ClientID, tblrefmarket.MarketDesc, tblclientinfo.LastName, tblledger.LoanAmount, "
+ "tblledger.DateStarted, tblledger.DailyPay, tblledger.Expiry FROM tblclientinfo Inner Join tblbusinessinfo ON tblbusinessinfo.ClientID = tblclientinfo.ClientID "
+ "Inner Join tblrefmarket ON tblbusinessinfo.MarketID = tblrefmarket.MarketID "
+ "Inner Join tblledger ON tblledger.ClientID = tblclientinfo.ClientID where MarketDesc = ?";

// (2)
String sumSQL = "SELECT ClientID, sum(tblloanpayment.AmountPaid) as sum FROM tblloanpayment where tblloanpayment.ClientID= ? ";

// (3)
String balSQL = "SELECT (SELECT tblLedger.LoanAmount from tblLedger WHERE tblLedger.ClientID = ?) - (SELECT SUM(tblLoanPayment.AmountPaid) "
+ "FROM tblLoanPayment WHERE tblLoanPayment.ClientID = ?) as balance FROM dual; ";

I have executed this 3 queries to display informations on a jTable. And it was successful.

Now my problem is when I am generating the report (or print) using JasperReports.

I can only display the 1st query since it is inside the database. While query 2 and 3 are not. They are just computations of the payments made in query 1.

How can I join this so that I can be able to display all necessary informations?

Here's my code:

private void cmdPrintActionPerformed(java.awt.event.ActionEvent evt) {
    int row = tableMarket.getSelectedRow();
    try {
        JasperDesign jasperDesign = JRXmlLoader.load("notes receivables.jrxml");
        String sql = "SELECT tblClientInfo.ClientID, tblrefmarket.MarketDesc, tblclientinfo.LastName, tblledger.LoanAmount, "
                + "tblledger.DateStarted, tblledger.DailyPay, tblledger.Expiry FROM tblclientinfo Inner Join tblbusinessinfo ON tblbusinessinfo.ClientID = tblclientinfo.ClientID "
                + "Inner Join tblrefmarket ON tblbusinessinfo.MarketID = tblrefmarket.MarketID "
                + "Inner Join tblledger ON tblledger.ClientID = tblclientinfo.ClientID where MarketDesc = '" + tableMarket.getModel().getValueAt(row, 0).toString() + "'";
        JRDesignQuery newQuery = new JRDesignQuery();
        newQuery.setText(sql);
        jasperDesign.setQuery(newQuery);
        JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
        JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, null, conn);
        JasperViewer.viewReport(jasperPrint);

    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, e);
    }
}

This code displays only the first query.

Upvotes: 0

Views: 430

Answers (2)

user359040
user359040

Reputation:

You are already selecting tblLedger.LoanAmount in your first query, so the only additional information required from both your second and third queries is sum(tblloanpayment.AmountPaid). Try:

SELECT c.ClientID, 
       m.MarketDesc, 
       c.LastName, 
       l.LoanAmount, 
       l.DateStarted, 
       l.DailyPay, 
       l.Expiry,
       s.sumPaid,
       l.LoanAmount - s.sumPaid as balance
FROM tblclientinfo c
Inner Join tblbusinessinfo b ON b.ClientID = c.ClientID 
Inner Join tblrefmarket m ON b.MarketID = m.MarketID
Inner Join tblledger l ON l.ClientID = c.ClientID 
left join (SELECT ClientID, sum(AmountPaid) as sumPaid 
           FROM tblloanpayment group by ClientID) s on c.ClientID = s.ClientID 
where m.MarketDesc = ?

Upvotes: 1

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79838

Remove the restriction by client id in the second and third queries. Make them into subselects (that is, put them in parentheses) and make a big select that joins all three subselects on client id.

Edit

So if the three queries are query1, query2, query3, you'd end up with

select ... from (query1) baseSql 
join (query2) sumSql on baseSql.clientId = sumSql.clientId  
join (query3) balSql on baseSql.clientId = balSql.clientId

You may need to use left join instead of join if there are rows in baseSql that are missing from sumSql or balSql.

Upvotes: 0

Related Questions