Base: query a query?

[UserError]

Hello, I’m running Base Version 4.2.8.2 on Linux Mint 17.
I’m trying to rebuild/replicate a small Access db I made a while ago.
In Access I could query a query, but don’t seem able to do this - getting a “The data content could not be loaded.”

error SQL Status: 37000 Error code: -70

“The SQL command leading to this error is:
SELECT “ID2” AS “ID2”, “FirstName” AS “FirstName”, “LastName” AS “LastName”, etc etc”

I’ve tried to research this info but get out of my depth very quickly as it’s mostly about servers & SQL etc.
I’ve tried altering elements that look like they might be the problem (eg ORDER), but no joy either.
I’ve tried running the underlying query & leaving it open. Nope.

I’ve fallen at the first hurdle with Base, so any help would be appreciated.
Many thanks

[doug]

You can accomplish your objective in either of two ways.

1. Save the queries as ‘views’.

---

Do this by browsing to Tables and then clicking Create View.

You then will see the query builder. This option is much closer to the functionality in the other leading desktop database system. I think the difference is that the work of the querying is done entirely within the database server this way, and some back-ends (like MySQL) have limitations on the kinds of queries that can be saved as a View (no internal subqueries – but you can refer to other views).

That probably will be your choice, and will be familiar.

2. Write your stacked queries as a single query with a subquery.

---

To do this, under the Query tab you will need to select Create Query in SQL View. Then, you would re-write your two queries as one, as follows:

SELECT `sub`.`lastname`, `sub`.`firstname`
FROM (
  SELECT `t1`.`lastname`, `t1`.`firstname`
  FROM `t1` AS `t1`
) AS `sub`

Just go into your first query in SQL View, copy the SQL then go into your second query, and where it would be FROM t2, make a parenthesis and inside that add the first query. Then name the subquery.

FWIW, if you do option two, avoid reopening in the Query Builder. Just use SQL View as the Query Builder is buggy and sometimes renders good queries unusable.

(if this answered your question, accept the answer by clicking the check mark () to the left)

[UserError]

Thanks for this. I had a lot of trouble with Base so I’m trying out SQLite/Kexi to see if that will do what I need & SQLite/Spatialite can be used in my GIS