CODEWITHSUNDEEP
androidsqlsqlite
Dezork
Dezork

Reputation: 11

How to get data from two unrelated tables sqlite?

I have two tables.

table_1: id, title, img_url, url, gif_url, date
table_2: id, title, url, date

Now I only get data from table 1 like this

SELECT * FROM table_1 ORDER BY date DESC LIMIT 10

How can I do the same for two tables with different amount of columns?

Upvotes: 0

Views: 230

Answers (1)

Yunnosch
Yunnosch

Reputation: 26763

You can use UNION ALL if you fill up the missing columns,
e.g. by inserting constant values (like string '-', assuming that your urls are text) instead.

SELECT * FROM table_1 
UNION ALL
SELECT id, title, '-' as img_url, url, '-' as gif_url, date FROM table_2
ORDER BY date DESC LIMIT 10;

Testing this would be much easier, if a MCVE were available.

I used this as (hopefully basically equivalent) MCVE foundation:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE table_1 (a int, b int, c int);
INSERT INTO table_1(a,b,c) VALUES(1,5,7);
INSERT INTO table_1(a,b,c) VALUES(2,10,14);
CREATE TABLE table_2 (a int, c int);
INSERT INTO table_2(a,c) VALUES(3,11);
INSERT INTO table_2(a,c) VALUES(6,22);
COMMIT;

And the structurally identical test code:

select * from table_1
UNION ALL
select a, 42 as b, c from table_2
order by a LIMIT 3;

Gives the output:

a                     b           c
--------------------  ----------  ----------
1                     5           7
2                     10          14
3                     42          11

Note the line from table_2, starting with "3" and containing the 42 instead of "b". The 42 is the integer-analogon to the string '-'.

Upvotes: 1

Related Questions