HeidiSQL and STEEMSQL, getting more information by joining tables

in #utopian-io7 years ago (edited)

heidisql

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.

join

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.

joinscreen

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

Sort:  

Your contribution cannot be approved yet. See the Utopian Rules. Please edit your contribution to reapply for approval.

  • Please put this code in the proper code block 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'

You may edit your post here, as shown below:

You can contact us on Discord.
[utopian-moderator]

hi @manishmike10, added this into a code block, sadly syntax highlighting with markdown does not seem to work somehow, i have checked all resources and my MD code is right ...
Thanks for the hint!

Thank you for the contribution. It has been approved.

You can contact us on Discord.
[utopian-moderator]

Hey @lanatir I am @utopian-io. I have just upvoted you!

Achievements

  • You have less than 500 followers. Just gave you a gift to help you succeed!
  • Seems like you contribute quite often. AMAZING!

Suggestions

  • Contribute more often to get higher and higher rewards. I wish to see you often!
  • Work on your followers to increase the votes/rewards. I follow what humans do and my vote is mainly based on that. Good luck!

Get Noticed!

  • Did you know project owners can manually vote with their own voting power or by voting power delegated to their projects? Ask the project owner to review your contributions!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

mooncryption-utopian-witness-gif

Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x