CODEWITHSUNDEEP
sqlsqlite
von spotz
von spotz

Reputation: 895

select table to select from, dependent on column-value of already given table

for my intention I have to select a table to select columns from dependent on the column-value of an already given table.

First I thought about a CASE construct, if this is possible with sqlite.

SELECT * FROM
CASE IF myTable.column1 = "value1" THEN (SELECT * FROM table1 WHERE ...)
ELSE IF myTable.column1 = "value2" THEN (SELECT * FROM table2 WHERE ...)
END;

I am new to SQL. What construct would be the most concise (not ugly) solution and if I cannot have it in sqlite, what RDBM would be the best fit?

Thanks

Upvotes: 2

Views: 198

Answers (1)

Yunnosch
Yunnosch

Reputation: 26703

Here is a proposal for associating a value from one of two tables for each entry in mytable. I.e. this is making the assumption that mytable does not only contain a single entry for choosing the secondary table. For details on what this means, see "MCVE" at the end of this answer.

If you want to switch between two secondary tables, based on a single entry in main table, see at the very end of this answer.

Details:

  • a hardcoded "value1"/"value2" as column1 added on the fly to the result from secondary tables
  • joining by the faked colummn1 and a secondary join-key, assumption here id
  • a union all to make a single table from both secondary tables (including the fake column1)

select * from mytable left join (select 'value1' as column1, * from table1 UNION ALL select 'value2' as column1, * from table2) using(id, column1);

Output (for the MCVE provided below, "a-f" from table1, "A-Z" from table2):

value1|1|a
value2|2|B
value1|3|c
value1|4|d
value2|5|E
value2|6|F

MCVE:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mytable (column1 varchar(10), id int);
INSERT INTO mytable VALUES('value1',1);
INSERT INTO mytable VALUES('value2',2);
INSERT INTO mytable VALUES('value1',3);
INSERT INTO mytable VALUES('value1',4);
INSERT INTO mytable VALUES('value2',5);
INSERT INTO mytable VALUES('value2',6);
CREATE TABLE table2 (value varchar(2), id int);
INSERT INTO table2 VALUES('F',6);
INSERT INTO table2 VALUES('E',5);
INSERT INTO table2 VALUES('D',4);
INSERT INTO table2 VALUES('C',3);
INSERT INTO table2 VALUES('B',2);
INSERT INTO table2 VALUES('A',1);
CREATE TABLE table1 (value varchar(2), id int);
INSERT INTO table1 VALUES('a',1);
INSERT INTO table1 VALUES('b',2);
INSERT INTO table1 VALUES('c',3);
INSERT INTO table1 VALUES('d',4);
INSERT INTO table1 VALUES('e',5);
INSERT INTO table1 VALUES('f',6);
COMMIT;

For selecting between two tables based on a single entry in main table (in this case "mytable2":

select * from table1 where (select column1 from mytable2) = 'value1'
 union all
select * from table2 where (select column1 from mytable2) = 'value2';

Output (with mytable2 only containing 'value1'):

a|1
b|2
c|3
d|4
e|5
f|6

Upvotes: 1

Related Questions