How to select multiple tables at once?

Question

SQLite

Let's say I have a people table with 3 columns: id, age, name. There is a hobby_hunt table, where there are two columns: id, time. I need to get id and name, time for a post with a specific id. (two tables are just related by id)

Answer 1

There are 2 tables, of the following form:

-- Запрос на создание таблицы с именем people
CREATE TABLE people (
id INTEGER PRIMARY KEY AUTOINCREMENT,
age INTEGER NOT NULL,
name TEXT NOT NULL
);
-- Запрос на создание таблицы с именем hobby_hunt
CREATE TABLE hobby_hunt (
time_date TEXT NOT NULL,
auth_id INTEGER NOT NULL,
FOREIGN KEY (auth_id) REFERENCES people(id)
);
-- Запрос на добавление данных в таблицу
INSERT INTO people (id, name, age)
VALUES (1, 'Джек Лондон', 40);
INSERT INTO people (id, name, age)
VALUES (2, 'Лев Толстой', 82);
-- Запрос на добавление данных в таблицу
INSERT INTO hobby_hunt (time_date, auth_id)
VALUES ('2019-03-11 21:21:21.000', 1);
INSERT INTO hobby_hunt (time_date, auth_id)
VALUES ('2019-03-11 21:22:22.000', 2);
INSERT INTO hobby_hunt (time_date, auth_id)
VALUES ('2019-03-11 22:22:22.000', 2);

Sampling in sqlite by INNER JOIN:

-- SQLite
SELECT
 people.id, people.name, hobby_hunt.time_date
FROM
 people
INNER JOIN hobby_hunt ON hobby_hunt.auth_id = people.id;

link to an example in the documentation
Specifically, you can restrict by id like this:

-- SQLite
SELECT
 people.id, people.name, hobby_hunt.time_date
FROM
 people
INNER JOIN hobby_hunt ON hobby_hunt.auth_id = people.id WHERE hobby_hunt.auth_id = 2;