JOIN and GROUP BY DATES but missing empty rows

Question

Maybe I'm tired, but can someone please explain to me why I'm not getting the empty rows?

I want this:

230901
id from join
id from join
id from join
230902
no rows matches in table B
230903
id from join
id from join
id from join
id from join
id from join
230904
no rows matches in table B

Now it gives me:

230901
id from join
id from join
id from join
230903
id from join
id from join
id from join
id from join
id from join

Table A does have all dates, table B only have some dates and I can't understand what to do to get dates echoed even if B hasnt that date.

$query = "SELECT B.B_id, B.B_time, B.B_date, B.B_start, B.B_end,
A.A_date, A.A_id
 FROM A
  LEFT JOIN B ON DATE(A.A_date) = B.B_date

 WHERE A.A_id = $use_id AND A.A_date between '$first' and '$last' AND B.B_id = $use_id "; 
$result = $db->query($query);


$appointments = array();

while ($row = mysqli_fetch_assoc($result))
{
    if (!isset($appointments[$row['A_date']]))
    {
        $appointments[$row['A_date']] = array();
    } 

    $appointments[$row['A_date']] []= $row;
}

foreach ($appointments as $date => $items)
{



    echo "<h1>{$date}</h1>";

    echo "<ul>";

    foreach ($items as $item)
    {
        echo "<li>{$item['B_date']} {$item['B_id']} {$item['B_start']}:{$item['B_end']} " . 
             "({$item['B_time']})</li>";
    }

    echo "</ul>";

}

Answer 1

SELECT B.B_id, B.B_time, B.B_date, B.B_start, B.B_end, A.A_date, A.A_id
FROM A
LEFT JOIN B ON DATE(A.A_date) = B.B_date
WHERE A.A_id = $use_id 
  AND A.A_date between '$first' and '$last' 
  AND B.B_id = $use_id "  

the last AND statement Thi is the problem. The join results in records in A not in B being included, then get excluded because b.B_ID will be null which will never match the user_id as Null can't be compared to a string so those records you expect to be retained by the left join are now excluded.

Instead move it to the join or the left join is negated and it behaves like an inner join.

SELECT B.B_id, B.B_time, B.B_date, B.B_start, B.B_end, A.A_date, A.A_id
FROM A
LEFT JOIN B ON DATE(A.A_date) = B.B_date  AND B.B_id = $use_id " 
WHERE A.A_id = $use_id 
  AND A.A_date between '$first' and '$last'