CODEWITHSUNDEEP
mysqlsqlselect
Jonny
Jonny

Reputation: 2917

MySql Keeping reusing Result of an SQL Query

So two questions really: 

SELECT CurrencyRateID, Rate, Markup
FROM currency_rate 
WHERE CurrencyID = (SELECT CurrencyID FROM currency WHERE BaseCurr = 1) **A** AND
DateTime = (SELECT MAX(DateTime) 
            FROM currency_rate 
            WHERE CurrencyID = **B**)
  1. Why does this query give me an Error Code: 1054. Unknown column 'CurrercyID' in 'field list'
  2. How can I use the result of the first SELECT Statement A in B

Upvotes: 0

Views: 671

Answers (2)

MatBailie
MatBailie

Reputation: 86706

Hopefully your currency_rate table has a unique identifier column.

If it does, you can merge queries A and B into a single query in the WHERE clause...

SELECT
  CurrencyRateID, Rate, Markup
FROM
  currency_rate 
WHERE
  uniqueID = (
              SELECT uniqueID
              FROM   currency_rate
              WHERE  CurrencyID = (SELECT CurrencyID FROM currency WHERE BaseCurr = 1)
              ORDER BY DateTime DESC LIMIT 1
             )

If not, you may just have to repeat yourself...

SELECT CurrencyRateID, Rate, Markup
FROM currency_rate 
WHERE CurrencyID = (SELECT CurrencyID FROM currency WHERE BaseCurr = 1)
AND DateTime = (SELECT MAX(DateTime) 
            FROM currency_rate 
            WHERE CurrencyID = (SELECT CurrencyID FROM currency WHERE BaseCurr = 1))

Note: Even though the code repeats itself, it's likely that MySQL will notice the repeition and only perform the query once.

EDIT:

I just noticed that you could simplify the first option to just use the sub-query...

  SELECT CurrencyRateID, Rate, Markup
  FROM   currency_rate
  WHERE  CurrencyID = (SELECT CurrencyID FROM currency WHERE BaseCurr = 1)
  ORDER BY DateTime DESC LIMIT 1

Upvotes: 1

hgulyan
hgulyan

Reputation: 8239

Check if currencyID column exists in currency table and change query this way, so you can use statement A in where clause.

SELECT CurrencyRateID, Rate, Markup
FROM currency_rate cr inner join 
   (SELECT CurrencyID FROM currency WHERE BaseCurr = 1) **A**
   ON cr.CurrencyID = a.CurrencyID 
WHERE cr.DateTime = (SELECT MAX(DateTime) 
            FROM currency_rate 
            WHERE CurrencyID = A.CurrencyID)

Upvotes: 1

Related Questions