Show QSqlQueryModel in QTableView without sort or union

[euerka]

Let's say there are two tables,

Table 1:
course_ID
1
2
...

Table 2:
class_ID........student_ID............course_ID
......1.................1......................... .2
......1.................2......................... .1
......1.................3......................... .2
......1.................4......................... .1

My code as follow:

courseModel=new QSqlQueryModel();
courseModel->setQuery("SELECT *FROM Table1 WHERE course_ID IN (SELECT  course_ID FROM Table2 WHERE class_ID=1)");
ui->courseModelView->setmodel(courseModel);
ui->courseModelView->show();

courseModel=new QSqlQueryModel();
courseModel->setQuery("SELECT *FROM Table1 WHERE course_ID IN (SELECT  course_ID FROM Table2 WHERE class_ID=1)");
ui->courseModelView->setmodel(courseModel);
ui->courseModelView->show();

To copy to clipboard, switch view to plain text mode 

result is :

course_ID
1
2

what i want is without sorting and union:

course_ID
2
1
2
1

second question is that since i use sqlite3 as database, table is column head (horizontal), but i want show it vertical as:

course_ID 2 1 2 1

in QTableView

Any solution or ideas? Thank you in advance!

[ChrisW67]

There is no sorting or union involved in your query. You are asking for all the rows in table 1 where the course_id is in table 2 with classID = 1. There are only two rows in table 1 and therefore at most two rows in the output.

Assuming there are actually other columns in table 1 then what you want is a join.

SELECT table1.* 
FROM table1 INNER JOIN table2 
ON table1.courseID = table2.courseID
WHERE table2.classID = 1

SELECT table1.* 
FROM table1 INNER JOIN table2 
ON table1.courseID = table2.courseID
WHERE table2.classID = 1

To copy to clipboard, switch view to plain text mode 

If there are no other columns in table 1 then you don't need that table at all.
Unless you explicitly apply some sort order to the data there are no guarantees about the order of the output, though usually you get them in the order they were inserted.

If you wish to transpose the rows and columns from the model then you should either:

• Use a proxy model to transpose the data. There's an example [wiki]Transpose Proxy Model[/wiki] in the wiki.
• Use a more complicated query to extract the data that way, but this comes with lots of limitations.