Combining multiple SQL queries

Question

Currently, in order to get the data I need, I need to execute several SQL statements:

SELECT pubkey.pubkey_id FROM pubkey WHERE pubkey.pubkey_hash = (input data [1]) SELECT txin_corr.tx_id FROM txin_corr WHERE txin_corr.pubkey_id = (pubkey.pubkey_id from previous query [max. 1]) SELECT txin_corr.pubkey_id FROM txin_corr WHERE txin_corr.tx_id = (txin_corr.tx_id from prev.qry. [n]) SELECT pubkey.pubkey_hash FROM pubkey WHERE pubkey.pubkey_id = (txin_corr.pubkey_id from prev.qry. [n]) 

The first request is not a problem, because I need to do this only once. But I am wondering if there is a way to combine (at least) the last three queries. Since the db is quite large (~ 20 GB), I think that a “good request” can significantly speed up the process.

What I'm doing: for a given pubkey_id / pubkey_hash , get all tx_id from txin_corr that contain this pubkey_id on the same line. Then get all pubkey_id from txin_corr , where the string contains the extracted tx_id s. Finally, get all pubkey_hash for the now found pubkey_id s.

Answer 1

The earlier answer is correct: the key is to join the tables together several times. But there is a one-to-many relationship, so it will be necessary to leave external connections, not just internal connections.

 SELECT pk2.pubkey_hash FROM pubkey pk INNER JOIN txin_corr tc ON pk.pubkey_id = tc.pubkey_id LEFT OUTER JOIN txin_corr tc2 ON tc.tx_id = tc2.tx_id LEFT OUTER JOIN pubkey pk2 ON tc2.pubkey_id = pk2.pubkey_id WHERE pk.pubkey_hash = (input data) 

Answer 2

Here is one way. I will not argue that this is the most efficient way to do this, but it should work in any database.

The trick connects to each table several times with a different prefix, so each time you can map different columns. So you join txin_corr to match the initial pubkey_id, and then join it to get a complete list of related identifiers. Then join back to pub to get entries matching this new list of identifiers.

 SELECT pk2.pubkey_hash FROM pubkey pk INNER JOIN txin_corr tc on pk.pubkey_id = tc.pubkey_id INNER JOIN txin_corr tc2 on tc.tx_id = tc2.tx_id INNER JOIN pubkey pk2 on tc2.pubkey_id = pk2.pubkey_id WHERE pk.pubkey_hash = (input data) 

Answer 3

This may not be the best way to do this, but you can simply put all your favorites together. if any of the queries returns more than one result, you can simply change the ship from = to in relation and process more than 1 result

 SELECT pubkey.pubkey_hash FROM pubkey WHERE pubkey.pubkey_id = (SELECT txin_corr.pubkey_id FROM txin_corr WHERE txin_corr.tx_id = (SELECT txin_corr.tx_id FROM txin_corr WHERE txin_corr.pubkey_id = (SELECT pubkey.pubkey_id FROM pubkey WHERE pubkey.pubkey_hash = (input data [1] ) ) )