CODEWITHSUNDEEP
mysql
Julius Barra
Julius Barra

Reputation: 59

Mysql - Divide 1 query for another one in the same table

A table with currency, rates, temptime - I want to divide some selected rates for the USD rate in the same temptime . .

CREATE TABLE IF NOT EXISTS `currency_rates` (
   `id` INT NOT NULL AUTO_INCREMENT
 , `currency` VARCHAR(254) NOT NULL
 , `rate` FLOAT NOT NULL
 , `temptime` DATE NOT NULL
 , PRIMARY KEY (`id`)
) ENGINE = MyISAM;


INSERT INTO `currency_rates` 
(currency, rate, temptime)
VALUES 
('USD', '1.232', '2018-03-16'),
('AUD', '133.82', '2018-03-16'),
('CAD', '1.99', '2018-03-16'),
('EUR', '1.6654', '2018-03-16'),
('USD', '1.242', '2018-03-17'),
('AUD', '132.82', '2018-03-17'),
('CAD', '1.79', '2018-03-17'),
('EUR', '1.4654', '2018-03-17'),
('USD', '1.272', '2018-03-19'),
('AUD', '123.82', '2018-03-19'),
('CAD', '1.765', '2018-03-19'),
('EUR', '1.254', '2018-03-19');


$result1 = mysql_query("SELECT currency, rate, temptime  FROM `currency_rates` WHERE currency = 'USD' ORDER BY temptime asc"); 

$result2 = mysql_query("SELECT currency, date, rate FROM `currency_rates` WHERE currency IN ('AUD', 'CAD', 'EUR') ORDER BY temptime asc"); 

$result3 = I want that every rate from $result2 is divided for the USD rate from $result1 with the same temptime.

Thanks

Upvotes: 2

Views: 45

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a cross join the USD query

$result2 = mysql_query("SELECT a.currency, a.date, a.rate/b.rate
          FROM `currency_rates` a 
          CROSS JOIN  `currency_rates` b 
          WHERE b.currency = 'USD'
          AND  a.currency IN ('AUD', 'CAD', 'EUR') 
          ORDER BY a.temptime asc");

and if you need the result for each temptime (as suggested by RaymondNijland)

$result2 = mysql_query("SELECT a.currency, a.date, a.rate/b.rate
          FROM `currency_rates` a 
          INNER JOIN  `currency_rates` b  ON  a.temptime = b.temptime
          AND b.currency = 'USD'
          AND a.currency IN ('AUD', 'CAD', 'EUR') 
          ORDER BY a.temptime asc");

Upvotes: 1

Related Questions