Welcome to the 3rd tutorial about HeidiSQL and Steemit
Maybe you remember the last Tutorial in this series, we stopped at the use of the save snippet function to be able to later reuse your SQL statements. This is extremely helpful and will save you a lot of time.
In this tutorial i will show you how to connect tables with SQL in order to receive more informations at one glance.
SQL join
The join operator within the SQL language lets you connect tables in order to retrieve informations out of both of them. In general, to avoid update problems, the data is clustered in several tables and not stored in one, this brings along a lot of pros. But if you want to bring this data together again you need to use a special feature call join.
There are different types of join, but since this is no direct SQL tutorial i will skip to explain but just post a picture that show how the different types work.
This join needs to be done on a joining criteria, a field which in both tables needs to be existing, like a customer number or in our case the name of an account.
Joining Steemit tables with HeidiSQL
Again please log into STEEMSQL using HeidiSQL, like we did in the first tutorial
Lets say we would want to see the reputation for all the accounts that originated a transaction in the last 24 hours. The transactions are in TxTransfers, but the reputation score is in Accounts. So we have to use join to bind those two tables together!
First we got to inspect the tables and their contents to see on which field we can join those tables.
In TxTransfers, the account name is in two fields, "from" and "to", but since we said we wanted to see the reputation of the origin we have to use the "from" column.
In Accounts the column withe the account name is called "name".
So far so good, with this information we can write the statement as follows:
SELECT Accounts.name, Accounts.reputation, TxTransfers."from", TxTransfers."to", TxTransfers.amount FROM TxTransfers JOIN Accounts ON Accounts.name = TxTransfers.from where TxTransfers.timestamp > '2017-12-23'
In the following screenshot you see the raw data that this SQL Statement produced and it is displaying the information that we wanted, Name and Reputation out of Account and the transaction data out of TxTransfers.
Try to play around with the data for yourself and see what useful combinations you can find!
Thanks alot for reading!
Lanatir
Posted on Utopian.io - Rewarding Open Source Contributors