 Hey everybody, this is Brian and welcome to the 57th Q-tutorial with C++ and GUI programming. Today we're going to be covering the infamous Q-SQL relational table model. That's a mouthful. Let me show you what's going on here. We've got our trusty database here and we've got our people table. Let's just open that. See we've got Bob Hope, Will Smith, and of course my daughter Heather Carons. And we've added another table here called wages. If we open this up, you see it has an ID, a person column, which is just the identity specifier of the people table and their salary. So if you flip over you can see, you know, Bob is one, Will is two, Heather's three. So Bob is one, is 200, Will $350 and my daughter is a measly $10.37 an hour. I have no idea how much any of these people make. I'm sure Bob Hope and Will Smith made much more and I'm sure my daughter makes less. So I apologize in advance, but that's the data we got to work with. So let's flip into Qt Creator and go new, create a GUI project and we want to go. Let's see here. I'm running out of names for these. Call it SQL-REL. Next and we're just going to, we're just going to do dialogue. Next, next, finish, finish. And let's throw a table view on here. Let's close that. All right, now let's get to, get to some code here. Let's open up dialogue.h. And the first thing you want to do of course is add your includes. So we should probably go in here and add in a reference to SQL. I know we've probably beaten this whole subject to death. You know, of course the thing will scan it here. There we go. Let's just add in and of course we need to add in SQL. So let's just say, oops, I did get asked the question the other day. What should you be using as far as a database platform? And in all honesty, it really doesn't matter. Use what you feel comfortable with. I started off as a Windows guy. So of course I use SQL, but there are many, many other database platforms out there. So we need our database. We'll say DB and we need our SQL. Let's see here. SQL relational table model. That is a big one right there. Make that a pointer. Now, if you are just joining me and you have no idea what I'm doing, I beg you, go watch the previous tutorials where we cover Qt with SQL from the very beginning. This is probably going to be the last one I do for a while, at least for SQL. Sorry, I should pause. I know some of you were probably like, oh, no, make more videos. But yes, we will make more videos. I just want to dive into different subject matter. You can only do SQL for so long before you just go mentally nuts, kind of like some of the DBAs I work with. So we want to make our database. Sorry, I should say we want to add the driver. So we'll say add database and we want to do a QODBC. That's the database driver we're working with. And we want to set the host name. And in my case, the host name is localhost. Hope if I spelled host right. And we are using SQL express. Once again, your underlying database platform is pretty much irrelevant. SQL works with just about every popular database platform out there. Did I just say SQL works with, sorry, Qt works with every popular database platform out there. And we will set the database name. And in my case, I'm using a DSN. We've covered this in previous tutorials, so I won't really cover that. All right, so now we need to DB open. And of course, we're going to close our connection when the dialog is closed. Or I should say when the dialog is destroyed. And now we can really get to the heart of the matter here. First thing I want to do is create our model. Q SQL relational table model. You think they'd come up with a smaller name, but it's very descriptive. It suits its purpose. Who am I to judge? The developers at Qt, Nokia are admittedly godlike in comparison to me. So we'll say set table because we need to set the primary table here. And we're going to set it to people. Now, the claim to fame with the SQL relational database. Or I'm sorry, the SQL relational table model is that you can set a relation between two tables. Or actually you can set between multiple tables. And I'll show you what I mean here. We'll say model dot set relation. And you want the column. So we're just going to say the first column or column zero. And now we need to create a SQL relation. So we'll say Q SQL relation. And we need to give it the alternate table, which if you remember is wages. And now we need to say what links them together. Well, the ID column in wages and the salary. I'm sorry, the ID column in people and the salary should be linked together. And don't worry, we're going to go back over all of this. I just want to kind of hash this out real quick. This is actually a lot of typing for a very small project. And I'm not a huge fan of typing. And then we'll say UI table. And then finally, we set the model. This is very simple model view that you're used to working with here. Let's save and compile and run. And if everything goes as planned, it will connect to the database. It will create a relation between the people and wages table. And here we go. You can see exactly what I'm talking about here. It's created our relation. And we've got Bob Hope. And there's his salary of 200. Will Smith. And there's his salary of 300. And my daughter with her salary of 1037. So that's how you create a relation between two tables and show them in the table view. Now, some of you out there are sitting here going, but Brian, there's an easier way. And yes, there is. You can have your DBAs do the work for you. Let's just flip into our database here. There's these things called views. And if you're unfamiliar with what a view is, we're going to create one real quick here. View is a way of displaying information from multiple tables. And a logical order. So we're going to say we want the people table and the wages table. And when you're done, just close. And you see there's our two tables. And I apologize. The screen real estate here is kind of kind of cramped. Let me actually just close some of that. And you can create a relation between the two. So for example, we want the people ID to be the person identifier in wages. So we'll just simply click and drag and select that. And you notice how it makes the SQL query for you. Select from DBO people, inner join. If you don't know what an inner join is, I beg you to go look it up. But basically, that just creates a relation between these tables and joins them on a specific field. In this case, we're joining them between the ID and the person, meaning those two have to match. And then we will of course do our, whoops, no column selected. Sorry about that. We'll select, let's see. First name, last name, and salary. We will then display the information. So you see Bob Hope, salary, Will Smith, salary, my daughter, salary. The trick here is that you can save this. And we'll say my view. And go ahead and close that. And let's just open the object explorer back up. And you see there's my view. And then you can just open up the view and voila. Yeah, there's your query. Now, if you're scratching your head wondering why in the world you would do this, well, you can treat that like a SQL query. For example, you can say, script, select, select, star from, and execute. And then you can do, you know, specific wares, you know, like where salary is less than 10 or whatever. But the point is, typically when you're working with a program, you want to keep as much of the database logic in the database as possible. That's why we pay DBAs. There are things like views and start procedures and et cetera, et cetera. The class that we've discussed, the QSQL relational table model, is simply a convenience class that makes it easier for you to do that. So if you don't want to muck around with, you know, building a view or creating a complex SQL query, you can just use that class and be done with it. So this is Brian. And thank you for watching. I hope you found this video educational and entertaining. And I want to say thank you guys for your support. I've actually gotten a couple donations. I haven't been asking for them, but I was very humbled and grateful to receive those donations. And you funded my website for about two months actually, which is very nice. I appreciate that very much. Thank you.