# Pagination using SQL Query

The previous example showed how to fetch a list of objects from a database table using Trillo Workbench. There are times when we want to fetch a list and for each object its related object. In the database terms, it is achieved using a join statement.

### Code: /lessons/Pagination\_using\_SQL\_Query

Steps

1. Let us say we have to paginate through a list of customers and at the same time fetch all line items of each customer and link with it.
2. The following query fulfills the requirement.

```
SELECT
  c.id AS customer_id,
  c.firstName AS first_name,
  c.lastName AS last_name,
  li.id AS line_item_id,
  li.description,
  li.price
FROM
  Customer_tbl c
JOIN
  LineItem_tbl li ON c.id = li.customerId
WHERE
  c.id = {{{id}}};

```

3. In the above query {{{id}}} is a template variable. Its value will be replaced by a value passed in parameter, such as (id = 1).
4. Let us see how it works in the workbench.
5. Select Queries from the main navigation of the workbench.
6. Using New Query, create a new query.
7. Give it some name.
8. Enter your query and execute it.
9. Examine the result.
10. The following steps show how to fetch the same tree using a function.
11. Let us make a copy of the above function as PaginationBySQLQuery. 
12. Set DataRequest.sql parameter as follows. It will instruct WB to give preference to the SQL statement for fulfilling the request.

```
dataRequest.setSql(<sql statement>);
```

13. Execute function and examine the result.