Join two tables brings mismatch values MYSQL [closed]

Posted on

Question :

I have two tables with >4million records i need to make a select query with where two columns match bring both tables value on this match and then i will insert that into a 3rth table:

This is table A: (bitfinex)

enter image description here

This is table B: (Kraken)

enter image description here

I need to do a SELECT where timestamp and exchange_pair matches, as you can see in this image from table B the same timestamp can have multiple exchange_pair, what i need to do is match those with the other table columns and bring both table content

enter image description here

this was the query i used at once but when analyzing the results i had some mismatch values on the joins ( i did not found the relation, it was not either timestamp or the exchange pair) i wonder if my query syntax is right for the result im looking for.

INSERT IGNORE INTO spreads(timestamp,exchange_pair,platform_a,platform_b,a_bid_price,b_bid_price, high_value,spread_percentage,spread)
SELECT B.timestamp, B.exchange_pair,'bitfinex' as platform_a,'kraken' as platform_b,
 B.bid,
 K.bid_price,
 (SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)),
 (SELECT (((ABS(K.bid_price - B.bid))*100)/(SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)))),
  ABS(K.bid_price - B.bid)  AS spread
FROM bitfinex AS B
JOIN kraken AS K
 ON B.timestamp = K.timestamp
JOIN kraken AS K2
 ON B.exchange_pair = K2.exchange_pair
WHERE K.exchange_pair = B.exchange_pair AND B.timestamp = K.timestamp ;

im still confused if using inner join or just join, also i added the “Where” clause to make sure exchange pair and timestamp matches.

Answer :

Point 1 : INNER JOIN & JOIN both are same thing.

Point 2 : No need to do an extra join on K2

You can use this :

INSERT IGNORE INTO spreads(timestamp,exchange_pair,platform_a,platform_b,a_bid_price,b_bid_price, high_value,spread_percentage,spread)
SELECT B.timestamp, B.exchange_pair,'bitfinex' as platform_a,'kraken' as platform_b,
 B.bid,
 K.bid_price,
 (SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)),
 (SELECT (((ABS(K.bid_price - B.bid))*100)/(SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)))),
  ABS(K.bid_price - B.bid)  AS spread
FROM bitfinex AS B
JOIN kraken AS K
 ON B.timestamp = K.timestamp
and 
 K.exchange_pair = B.exchange_pair  ;

Leave a Reply

Your email address will not be published. Required fields are marked *