How to join two query in MYSQL

Question

I have 2 MYSQL base queries which dependent on each other, here are my quires

@$query = "SELECT * FROM coins_tokens";
$row    = $db->Execute($query);

foreach ($row as $rowItem) {
    $name = $rowItem['ct_id'];

    @$sql1 = "SELECT * FROM historical_data  WHERE `name` = '".$name."' GROUP BY name LIMIT  30";
    $row2 = $db->Execute($sql1);
    foreach ($row2 as $rowItem2){ 
        $market_cap     = $rowItem2['market_cap'];
        if($market_cap >= 500000000){

        }
    }
}

It slow down my whole process and take lot of time to execute, as there are more then 1400 results in coins_tokens, then there are more then 600000 records again 1st table, in both table ct_id and name are conman.

And what I am trying to do is to get the currencies which have more then 500million market_cap in last 7 days. So am fetching the currencies from 1st table and there historical data from 2nd table and checking if market_cap there increased in last 7 days.

Here is the structure and data of historical_data table:

https://stackoverflow.com/questions/16222097/mysql-left-join-3-tables — Shafiqul Islam, Dec 26 '17 at 19:47
Didn't you learn anything from the answer given in your previous question? https://stackoverflow.com/questions/47969398/min-value-from-database-in-mysql — Funk Forty Niner, Dec 26 '17 at 19:54
@FunkFortyNiner finally i learn and i solve this issue by my own. Thanks for the motivation :P — Sara Tariq, Dec 26 '17 at 21:00
Possible duplicate of [PHP/MYSQL Join multiple tables](https://stackoverflow.com/questions/13684796/php-mysql-join-multiple-tables) — Wiguna R, Dec 26 '17 at 21:15

score -1 · Answer 1 · answered Dec 26 '17 at 21:12

-1

SELECT
    c.*,
    d.`date`,
    d.market_cap
FROM coins_tokens AS c
    LEFT JOIN historical_data AS d ON c.ct_id = d.name
WHERE d.market_cap >= '$mketcapgrter'
  AND DATE(d.`date`) >= CURRENT_DATE() - INTERVAL 30 DAY 
GROUP BY d.name 
ORDER BY d.market_cap DESC LIMIT 100

answered Dec 26 '17 at 21:12

Sara Tariq

27
6

Left join x ... where x = ... is the same as inner join, and group by has no place in this query – Strawberry Dec 26 '17 at 22:54
@Strawberry you did not have any idea what is going here, this query work, and there are lot of same entries for same name that's what group by use, and stop marking negative, – Sara Tariq Dec 27 '17 at 17:56

How to join two query in MYSQL

1 Answers1