Available in Non-Essentials editions of Navicat for MySQL, PostgreSQL, SQLite, MariaDB, and Navicat Premium, the Query Builder allows anyone to create and edit queries with only a cursory knowledge of SQL. In Part 1, we used it to write a query to fetch a list of actors that appeared in movies released during a given year. Today's blog will provide a more detailed overview on selecting output fields.
Today's Query
The query that we'll be building here today will again run against the Sakila sample database. It contains a number of tables themed around the film industry that cover everything from actors and film studios to video rental stores. Please refer to the Generating Reports on MySQL Data tutorial for instructions on downloading and installing the Sakila database.
Much like the previous blog, we will be building a query to fetch a list of actors that appeared in movies released during a given year. The difference is that this time we will make use of a view that lists actors for each title as a comma-delimited list.
Setting Field Associations
Dragging a table/view from the left pane to the Diagram Design pane or double-clicking it adds the table or view to query. The Query Builder will automatically include entity relationships where foreign key constraints have been declared. In this case, we'll be needing the film table and film_list view. They do not have a defined association, so we have to add one ourselves. To do that, just drag one field from one object to another and a line will appear between the linked fields - i.e. between film.film_id and film_list.FID.
The Query Builder will not only draw the association between the objects, but it will also add an INNER JOIN to the query:
With the tables/views selected, we are ready to choose out output fields.
Click the checkbox beside each field that you want to appear in your query results - i.e. film.title, film.film_id, film.release_year, and film_list.actors.
The fields you have selected in the Diagram Design pane will then be displayed in the Syntax pane, where then may then be modified clicking on the <Distinct>, <func> and <Alias> modifiers.
Using Functions
Clicking the <func> modifier opens a list of SUM, MAX, MIX, AVG, and COUNT aggregate functions. You may also enter another function via the Edit tab. For example, we could select the film_list.price field and enter "concat('$', film_list.price)" in the Edit tab to format the price. We can also move the field position by dragging it - for instance, before the actor list:
Field Aliases
When using functions, it's always a good idea to choose a more descriptive field name using an alias. For example, in this case we can simply go with the original field name of "price":
Here is the final query produced by the Query Builder:
SELECT
film.title,
film.film_id,
film.release_year,
concat('$', film_list.price) AS price,
film_list.actors
FROM
film
INNER JOIN film_list ON film.film_id = film_list.FID
And here are the results:
