我有两个表…
holidaymaster
id holidaydate cityid
1 18-04-2015 9
id coupondate cityname customer
1 15-04-2015 9 A
2 16-04-2015 9 B
3 17-04-2015 9 C
4 19-04-2015 9 D
我需要从两个表中获取数据通过连接这个…
需要检查holidaydate在holidaymaster中是否为空…然后对该日期回显null，否则回显1。
预期输出
customer coupondate Type
A 15-04-2015 1
B 16-04-2015 1
C 17-04-2015 1
NULL NULL NULL
D 19-04-2015 1
在上面的例子 - 18-04-2015在holidaymaster中可用，所以我需要在此显示null ..否则显示1。
请告诉我如何连接这两个表和匹配行…
和如果连接是不可能的，那么如何匹配两个不同的查询使用if else.
下面是我的代码。
i try with 2 diff query.
$data = $database->getRows("SELECT RE.* FROM receipt_entry RE LEFT JOIN city_master CM ON RE.city_name = CM.id WHERE CM.cityname = :cityname
AND str_to_date(RE.coupondate,'%d-%m-%Y') BETWEEN :fromdate AND :todate ORDER BY STR_TO_DATE(RE.coupondate,'%d-%m-%Y') ASC",
array(':fromdate'=>$formdate_sql,':todate'=>$todate_sql,':cityname'=>$cityname));
$holiday = $database->getRows("SELECT HM.holidaydate from holidaymaster HM LEFT JOIN city_master CM ON HM.cityid = CM.id WHERE CM.cityname = :cityname
AND str_to_date(HM.holidaydate,'%d-%m-%Y') BETWEEN :fromdate AND :todate",array(':fromdate'=>$formdate_sql,':todate'=>$todate_sql,':cityname'=>$cityname));
<?php if (is_array($data)) { foreach($data as $row) { ?>
<?php foreach($holiday as $row1) { ?>
<?php if($row['coupondate'] == $row1['holidaydate']) { ?>
<td>-</td>
<?php } else { ?>
<td>1</td>
<?php } ?>
<?php } } ?>
SELECT
customer, coupondate, IF(holidaydate is null, null, 1) AS type
FROM holidaymaster h
JOIN receiptentry r ON h.cityid = r.cityname
;