3

I need to select data from 2 tables based on 1 filed UID and display some from both tables.

Data from first table "a.symbol, a.order_type, a.price_open, a.time_open, a.size", and data from second table "b.bid, b.point".

When i use INNER JOIN to get data from both tables i get many results, but there is only 5 results that i have data for, when i use it without joins as you can see on 1st image.

Ok here is an query i have problems with:

SELECT a.symbol, a.order_type, a.price_open, a.time_open, a.size, b.bid, b.point
FROM trade_log a
INNER JOIN currency_rates b ON a.uid = b.uid
WHERE a.uid = '23' AND a.status = 'Open' AND a.order_type IN ('0', '1')
ORDER BY a.time_open ASC

So this is a data i get when i use query without inner join, from trade_log table

enter image description here

This i a data what i'm getting now with inner join, but i need just to add "bid" and "point" to table like on this upper image, but i don't know why he multiply results.

enter image description here

And this is a data from "currency_rates" where UID is 23

enter image description here

  • 1
    Obviously 5 x 4 = 20 – McNets Jan 13 '18 at 14:57
4

As far as you don't give us more information I'll throw a bet.

Using only uid to join both tables you get 20 records (5 x 4). I suppose you should add symbol.

SELECT     a.symbol, 
           a.order_type, 
           a.price_open, 
           a.time_open, 
           a.size, 
           b.bid, 
           b.point
FROM       trade_log a
INNER JOIN currency_rates b 
ON         a.uid = b.uid
AND        left(a.symbol, 6) = b.symbol
WHERE      a.uid = '23' 
AND        a.status = 'Open' 
AND        a.order_type IN ('0', '1')
ORDER BY   a.time_open ASC;
  • That is a problem, i can't compare "symbol" because they are different in tables. Look this is a table "trade_log" where "uid" = 23, image.prntscr.com/image/QzxvA8ryTRevOt-jSWFMJg.png and this is a table "currency_rates" where "uid" = 23 image.prntscr.com/image/rqDBL3XqR0WzzFQWh54XLw.png – Mario Jan 13 '18 at 15:17
  • For every "trade" there is only one "currency_rate" – McNets Jan 13 '18 at 15:19
  • yes there is only one "currency_rate" – Mario Jan 13 '18 at 15:20
  • How many rows get with my solution? – McNets Jan 13 '18 at 15:26
  • 1
    @McNets It appears that "symbol" in trade_log (table A) is always 6 letters + '.lmx', and "symbol" in currency_rates is always 6 letters, corresponding to the ones used in trade_log. I think this needs: ... ON a.uid = b.uid AND left( a.symbol, 6 ) = b.symbol ... – stefan Jan 13 '18 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.