CODEWITHSUNDEEP
mysqlsqlselectjoin
Mihai Matei
Mihai Matei

Reputation: 24276

MySQL JOIN returns unexpected values

I'm trying to do a simple mysql request and I'm having problems.

I have 2 tables defined like below:

currencies
______________________________________________________________________________________
currency_id | currency_name | currency_symbol | currency_active | currency_auto_update

exchange_rates
____________________________________________________________________________________________________
exchange_rate_id | currency_id | exchange_rate_date | exchange_rate_value | exchange_rate_added_date

What I want to do is to select the last row inside exchange_rates for the active currency.

I did it like this:

SELECT c.currency_id, c.currency_name, c.currency_symbol, c.currency_active, er.exchange_rate_id, er.exchange_rate_date, er.exchange_rate_value
FROM currencies c
LEFT JOIN (
    SELECT er1.exchange_rate_id, er1.currency_id, er1.exchange_rate_date, er1.exchange_rate_value
    FROM exchange_rates er1
    ORDER BY er1.exchange_rate_date DESC
    LIMIT 1
    ) AS er
ON er.currency_id=c.currency_id
WHERE c.currency_active='1'

This is returning me NULL values from the exchange_rates table, even if there are matching rows

I've tried to remove LIMIT 1 but if I do it like this is returning me all the rows for active currency, which is not the solution I want

How should this query look like?

Thanks!

Upvotes: 1

Views: 103

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT c.currency_id, c.currency_name, c.currency_symbol, c.currency_active, er.exchange_rate_id, er.exchange_rate_date, er.exchange_rate_value
FROM currencies c
LEFT JOIN (SELECT * FROM 
    (SELECT er1.exchange_rate_id, er1.currency_id, er1.exchange_rate_date, er1.exchange_rate_value
    FROM exchange_rates er1
    ORDER BY er1.exchange_rate_date DESC) AS A GROUP BY currency_id 
    ) AS er
ON er.currency_id=c.currency_id
WHERE c.currency_active='1'

Upvotes: 3

ChaosClown
ChaosClown

Reputation: 367

The idea of query look correct to me. your left join query should return the row with the newest exchange rate. you could use inner join here because there must be a currency refering to this...

SELECT c.currency_id, c.currency_name, c.currency_symbol, c.currency_active, er.exchange_rate_id, er.exchange_rate_date, er.exchange_rate_value
FROM currencies c
INNER JOIN exchange_rates_er1 e ON e.currency_id = c.currency_id
WHERE c.currency_active='1'
ORDER BY e.exchange_rate_date DESC
LIMIT 1

Upvotes: 0

Related Questions