Displaying one table data with respect to other table

Question

I'm stuck with this problem. I have 2 tables, Invoice and Invoice_Lines. I want to display my table in such a fashion that Invoice lines should be displayed below their respective Invoices. Here is my code:

$queryString = "SELECT * FROM Invoice";
        $roql_result_set = RightNow\Connect\v1_2\ROQL::query($queryString);

        echo "<table class=\"responstable\"><tr><th>ID</th><th>Account</th><th>Contact</th><th>Display Order</th><th>Date Created</th><th>Date Updated</th></tr>"; 
        while($roql_result = $roql_result_set->next())
        {
            while ($row = $roql_result->next())
            {   
                $lines = "SELECT * FROM Invoice_lines";
                $result_set = RightNow\Connect\v1_2\ROQL::query($lines);
                while($result = $result_set->next())
                {
                    while ($line = $result->next())
                    {                       
                        echo "<tr><td><a href=\"#\">" . $line['ID'] ."</a></td><td><a href=\"#\">" . $line['Invoice'] . "</a></td><td>" . $line['Line_Number'] ."</td><td>" . $line['DisplayOrder'] . "</td></tr>"; 

                        $ctime = date('F j, Y' , strtotime($row['CreatedTime']));
                        $utime = date('F j, Y' , strtotime($row['UpdatedTime']));

                        echo "<tr><td><a href=\"#\">" . $row['ID'] ."</a></td><td><a href=\"#\">" . $row['Account'] . "</a></td><td>" . $row['Contact'] ."</td><td>" . $row['DisplayOrder'] ."</td><td>" . $ctime ."</td><td>" .$utime. "</td></tr>";  
                    }
                }

            }
        }
        echo "</table>"; 

Answer 1

Try doing this,

SELECT Invoice.*, Invoice_lines.* 
  FROM Invoice
  LEFT JOIN Invoice_lines ON Invoice.ID = Invoice_lines.Invoice
   ORDER BY Invoice.Display_order, Invoice_lines.Display_order

Answer 2

You need to use a JOIN query here to construct a single resultset with content from both tables. A query like this will do the trick for you

SELECT Invoice.*, Invoice_lines.* 
  FROM Invoice
  LEFT JOIN Invoice_lines ON Invoice.ID = Invoice_lines.Invoice
   ORDER BY Invoice.Display_order, Invoice_lines.Display_order

But, please don't treat this query as a magic spell. Instead, learn how to use this core feature of SQL.

Answer 3

You don't have any exposure to MySQL through OSvC or its APIs. That tag should be removed from the question. The question is specific to OSvC (Oracle Service Cloud) and ROQL, which is application-layer specifc, not database specific.

If you have the tables joined through a relationship in the object designer as a parent and child, then you would use the ROQL join syntax, which is explained in detail in the docs. Joins are implied inner joins when using ROQL, but you can set outer joins as well. Assuming you want to get all invoice lines for contact 1234, and your Invoice object has a Contact relationship and a relationship titled Invoice_lines, then queryCSV would look something like this:

SELECT I.ID as InvoiceID, I.Invoice_lines.* FROM Invoice I WHERE I.Contact = 1234

Query Object (queryObject method) syntax would be slightly different since you do not specify columns, but I would use query in your case since you are outputting the data in a tabular format:

SELECT I, Invoice_lines FROM Invoice I WHERE I.Contact = 1234

You'll need to account for the 10,000 row limit in your logic; you might need a loop that pages through the results if you intend to show more than 10k rows on a page.

Also, it looks like you're merging view and controller code in your example. This is bad practice. You should be performing your queries from a model, returning the results of your query to the controller to pass to the view. The view would simply iterate through the array of results to display your table.