Получение данных из связанных таблиц в C++

Запрос, который достанет всех юзеров вместе с их городами, строят на команде LEFT JOIN.

Ее синтаксис выглядит следующим образом:

SELECT поля FROM имя_таблицы
	LEFT JOIN имя_связанной_таблицы ON условие_связи
WHERE условие_выборки

Разберем отдельные части синтаксиса этой команды.

Поля

Так как выборка идет из нескольких таблиц, выборка всех полей через * не будет работать. Следующий запрос выберет поля только из основной таблицы, но не из связанной:

SELECT *

Чтобы данные выбирались из всех таблиц, перед * указывают имя таблицы для выборки:

SELECT users.*, cities.*

Либо перечисляют нужные поля с указанием имени таблицы перед ними:

SELECT users.name, cities.name

Эти два способа имеют проблему. Если поля в таблицах имеют одинаковые имена, в результате запроса произойдет конфликт имен и останется только одно поле, а второго не будет. При чтении через sqlite3_column_text второе значение с тем же именем уже не достать.

Для решения проблемы конфликтные имена переименовывают через команду AS:

SELECT users.name, cities.name AS city_name

Связь

После команды ON указывают поля из двух таблиц, по которым осуществляется связь. В этом случае это поле id из таблицы с городами и поле city_id из таблицы с юзерами:

ON cities.id = users.city_id

Запрос

В итоге запрос, который достанет юзеров вместе с их городами, будет выглядеть следующим образом:

SELECT
	users.name, cities.name AS city_name
FROM
	users
LEFT JOIN cities ON cities.id = users.city_id

Строку SQL передают в подготовленный запрос, а колонку после AS читают по индексу или по псевдониму в тексте запроса:

#include <iostream>
#include <sqlite3.h>

int main() {
	sqlite3* db = nullptr;
	if (sqlite3_open("gd_users.db", &db) != SQLITE_OK) {
		std::cerr << sqlite3_errmsg(db) << "\n";
		sqlite3_close(db);
		return 1;
	}
	sqlite3_exec(
		db, "PRAGMA foreign_keys = ON",
		nullptr, nullptr, nullptr
	);
	sqlite3_exec(
		db,
		"CREATE TABLE cities (id INTEGER PRIMARY KEY, name TEXT)",
		nullptr, nullptr, nullptr
	);
	sqlite3_exec(
		db,
		"CREATE TABLE users (id INTEGER PRIMARY KEY, "
		"name TEXT, city_id INTEGER, "
		"FOREIGN KEY (city_id) REFERENCES cities (id))",
		nullptr, nullptr, nullptr
	);
	sqlite3_exec(
		db, "INSERT INTO cities (name) VALUES ('city1')",
		nullptr, nullptr, nullptr
	);
	sqlite3_exec(
		db,
		"INSERT INTO users (name, city_id) VALUES ('user1', 1)",
		nullptr, nullptr, nullptr
	);
	sqlite3_stmt* sel = nullptr;
	sqlite3_prepare_v2(
		db,
		"SELECT users.name, cities.name AS city_name "
		"FROM users "
		"LEFT JOIN cities ON cities.id = users.city_id",
		-1, &sel, nullptr
	);
	while (sqlite3_step(sel) == SQLITE_ROW) {
		const char* user = reinterpret_cast<const char*>(
			sqlite3_column_text(sel, 0)
		);
		const char* city = reinterpret_cast<const char*>(
			sqlite3_column_text(sel, 1)
		);
		std::cout << user << "\n";
		std::cout << city << "\n";
	}
	sqlite3_finalize(sel);
	sqlite3_close(db);
	return 0;
}

Пусть есть таблица с товарами и таблица с их категориями. Напишите запрос, который достанет названия товаров вместе с их категориями, и выполните его через sqlite3_prepare_v2.