mogican mogican - 2 months ago 6
MySQL Question

How to create my specific MySQL select?

I have 2 DB tables

Table1

[US_name1 : US]
[US_name2 : US]
[US_name3 : US]
[UK_name1 : UK]
[UK_name2 : UK]
[DE_name1 : DE]
[DE_name2 : DE]


...

and the Table2

...

[2014-05-01 : US_name1 : 150]
[2014-05-01 : US_name2 : 300]
[2014-05-01 : US_name3 : 12.2]
[2014-05-01 : UK_name1 : 18]
[2014-05-01 : UK_name2 : 24]


...

how can I get result from specific date (for example: all from 2014-04-30) in next data-format:

{[US] -> [name]=US_name1, [last]=150, [2014-05-01]=150, [2014-04-30]=103}
{[US] -> [name]=US_name2, [last]=300, [2014-05-01]=300, [2014-04-30]=786}
{[UK] -> [name]=UK_name1, [last]=18, [2014-05-01]=18, [2014-04-30]=362}


my query is incorrect:

SELECT t1.Country, t1.Name, t2.Date, t2.Name, t2.Value FROM t1, t2 WHERE t2.Date >='".$start."' ORDER BY `Date` DESC
if($res){
while($row=mysql_fetch_array($res, MYSQL_ASSOC)){
if(!isset($rez[$row['Name']]['last'])){$rez[$row['Name']]['last']=$row['Value'];}
if(!isset($rez[$row['Name']]['country'])){$rez[$row['Name']]['country']=$row['Country'];}
$rez[$row['Name']][$row['Date']]=$row['Value'];
}
}

Answer

You need a condition in your query to relate the two tables:

SELECT t1.Country, t1.Name, t2.Date as Date, t2.Name, t2.Value FROM t1, t2 WHERE t1.Name LIKE t2.Name AND t2.Date >='".$start."' ORDER BY `Date` DESC

Note you can't order by Date unless you alias t2.Date as Date, so I fixed that too.