So I've reset my database and I've made two sales, and as you can see, I have an order ID one, an
order ID two, and those correspond to these customers, Bruce Wayne and Clark Kent and these transactions.
So I have some data I can actually query.
Now, as you might have noticed, our information is actually stored in the orders table, the customer's
table and the transaction table.
So our query is going to affect or use all three tables.
So let's just open a query window right in the database and see if we can get some information in a
useful format.
So let's give ourselves some room and I'll start writing a query.
So I'll write select because this is a select and I want to select.
I definitely want the order ID and I'm going to prefix it with Oh, and I'll use that as an alias to
the table.
So I want odah id or dot widget id dot transaction idy modot customer I.D. and then I'll put this on
the next line.
Odon status idy oduor quantity as well, it looks good, oh, the amount, the amount of the transaction.
And I'll get the O'Dowd created up and the updated eye and I'll just finish the query right now to make
sure that works from orders.
Oh, and I'll say order by product created at descending to get the most recent ones.
First, let's make sure that query works.
So I'll run the query.
And there I have that information.
Now, I want more than just the order.
I also want the widgets and what the name of what they sold down here in my from I'll say left join
widgets w on and I'll join them on the obvious things.
Widget I.B. widget in the orders table is equal to me and I will add up here after created out the idea
of the widget so it doesn't name.
And that should be enough from the witness table, so let's get rid of the question of the comma and
run this query again.
And now I should have the widget name and the ID and I do OK.
So I definitely want the widgets.
I also want the transactions.
So left join transactions with the alias aliased on.
And once again, that transaction, aside from the orders table, equals t ID.
And then let's put the fuels we want from the transaction.
So we want the ID to ID, we already have that, but I'll get it in the amount which we already have,
but I'll get it again.
The currency, and we want the last four digits of the credit card, Ketut, last dash for.
And the expiry month and the expiry year.
The payment intented.
And the banker return code.
We're not going to use most of these, but we might at some point in the future, so we will grab it
and let's just run that and make sure that works.
Run current and there it is.
I have all the information, which is good.
OK, and the last bit of information we want is the customers.
Who is this sale for so left join customers si on and again, customer, the customer is in the orders
table and that's going to be equal to set up ID and we'll grab here just the ID the customer and see
the first name, see the last name and their email address, see the email and I should be able to run
this now run current and get that.
Now this query looks good, but of course it's actually going to get everything in the orders table
and we only want the ones that are actual orders for a single digit or sales for a single digit.
So let's put it where clause in here where it is recurring equals zero.
So that will get me the sales for any product, any widget.
That is not a recurring sale and I've run it again.
I should get exactly the same thing because I have no other sales in my database right now.
Run current and it works great.
So this will be a query.
So in the next lecture we'll use this query to build the necessary database function that we can call
from the back end or call from the front end or call from wherever we need to call it.
And we'll get started on that in the next lecture.