How to display content of multiple QSqlTableModels

Question

I have a MySql table, let's call it x:

CREATE TABLE x (
    Id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    A int unsigned NOT NULL,
    B int,
    FOREIGN KEY (A) REFERENCES y(Id)
);

And then I have another table, let's call it y:

CREATE TABLE y (
    Id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    First varchar(255),
    Last varchar(255)
);

I want to display table x in one QTableView and in place of column A from table x I want to display columns First and Last from table y from row whose Id is equal to A from table x.

Do you have any ideas? Let me know if my explanation is not clear enough.

Answer 1

You can use QSqlQueryModel with sql join query:

QSqlQueryModel *model = new QSqlQueryModel;
model->setQuery("SELECT x.Id, y.First, y.Last, x.B FROM x "
                "LEFT JOIN y ON x.A = y.Id");

QTableView *view = new QTableView;
view->setModel(model);
view->show();