Multiple Select statements in a single query in SQLite

Question

Is it possible to run multiple select statements in a single query in SQLite?

For instance:

SELECT ( SELECT ChestGemEffects.Value, Effect.Name FROM ChestGemEffects INNER JOIN Effect ON ChestGemEffects.EffectId = Effect.Id INNER JOIN Gems ON ChestGemEffects.GemId = Gems.Id WHERE ( Gems.[Key] = 'SG1' ) ) AS ChestEffects, ( SELECT WeaponGemEffects.Value, Effect.Name FROM WeaponGemEffects INNER JOIN Effect ON WeaponGemEffects.EffectId = Effect.Id INNER JOIN Gems ON WeaponGemEffects.GemId = Gems.Id WHERE ( Gems.[Key] = 'SG1' ) ) AS WeaponEffects, ( SELECT OthersGemEffects.Value, Effect.Name FROM OthersGemEffects INNER JOIN Effect ON OthersGemEffects.EffectId = Effect.Id INNER JOIN Gems ON OthersGemEffects.GemId = Gems.Id WHERE ( Gems.[Key] = 'SG1' ) ) AS OthersEffects; 

This gives me an error:

'Error executing query: only one result is allowed for SELECT, which is part of the expression'

Is something wrong with my expression or is it just not supported in SQLite?

thanks

Answer 1

Using the result of a subquery as a source table for a further query should be performed in FROM :

 SELECT * FROM (SELECT ...), (SELECT ...) 

However, this will be a cross join , which you do not need.

To simply add multiple tables (with the same number of columns), use UNION ALL :

 SELECT ChestGemEffects.Value, Effect.Name FROM ... ... UNION ALL SELECT WeaponGemEffects.Value, Effect.Name FROM ... ...