Mysql – Inner join returns duplicate results

MySQL

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

Best Answer

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;
Related Question