Display Query Results in a Different Query

[samueltmcf]

Hello,

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).

SELECT "ID", ( 1.0 * "MYDATE" / 365 ) AS "AGE" FROM ( SELECT "ID", DATEDIFF( 'day', "DOB", CURRENT_TIMESTAMP ) "MYDATE" FROM "Stock" )

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!

[Ratslinger]

Hello,

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.

[samueltmcf]

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

[Ratslinger]

Hello,

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

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"

Edit:

Easier method using the “Age” query:

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 (upper left area of answer). It helps others to know there was an accepted answer.

[samueltmcf]

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?

[Ratslinger]

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.

[samueltmcf]

Thank you for your time and helpful responses!!