Display Query Results in a Different Query


I have a query that is calculating the age of cattle. Each cow is in a table called stock which lists all of their information. I would like to be able to run a query that displays each cow’s information in addition to their age that was calculated in a different query. My thoughts are either I can use a query to do this or I wish there was a way to store the age calculated in the query in the main table (stock).

This is the code in my query calculating the age (it is called Age).


This is a query I have that lists all of the different cows and their information

SELECT "ID", "Type", "Status", "Name", "Tag #", "Breed/Color" FROM "Stock"

I have attempted several tutorials on subqueries but am not getting the syntax right and keep getting error messages when I try to add the Age query in parenthesis to the second query I showed.

All tips and any help are much appreciated!


Please, with future questions include specific LO version, OS and what database you are using (and connector when appropriate). This make a difference especially with SQL. Took a guess here you are using HSQLDB embedded.

I apologize, yes it is HSQLDB embedded and version, running on macOS 10.15.4


Just took your two statements (and added “AGE” to first) and used a Left Join:

image description

Here is the SQL for an easier copy:

SELECT "ID", "Type", "Status", "Name", "Tag #", "Breed/Color", "AGE" FROM "Stock"
Left Join
(SELECT "ID", ( 1.0 * "MYDATE" / 365 ) AS "AGE" FROM ( SELECT "ID", DATEDIFF( 'day', "DOB", CURRENT_TIMESTAMP ) "MYDATE" FROM "Stock" ))A ON "Stock".ID = A."ID"


Easier method using the “Age” query:

image description

SELECT "ID", "Type", "Status", "Name", "Tag #", "Breed/Color", "AGE" FROM "Stock", "Age" Where "Stock".ID = "Age".ID

For Joins see → SQL Joins

If this answers your question please tick the :heavy_check_mark: (upper left area of answer). It helps others to know there was an accepted answer.

Thank you! This works really well.

Could Left Join be implemented for simpler queries as well? For example If I wanted to make type and status the same column.

SELECT "Type","Status" AS "Class" FROM "Stock"

What would be the syntax for adding a simple query like that?

As for combining two fields into one, this is concatenation:

SELECT  "Type" || "Status" as "Class" FROM "Stock"

and with hyphen:

SELECT  "Type" || "-" || "Status" as "Class" FROM "Stock"

Can do without join.

Thank you for your time and helpful responses!!