Results not returning rows where second parameter should match null

Question

I'm trying to left join three tables using columns from the first and third tables as the filters. The second and third tables could have null values where I am trying to join. I want to be able to return all rows if I don't specify the second parameter (:store_number), but it only returns rows where the store_number is not null.

SELECT t1.host_name, t1.vcenter_name, t1.connection_state, t1.power_state, t2.cluster_name, t3.store_number, t3.district_number, t3.time_zone, t3.active
FROM host t1
LEFT JOIN cluster t2 ON t1.cluster_id = t2.cluster_id AND t1.vcenter_name = t2.vcenter_name
LEFT JOIN store t3 on t2.cluster_name IS NOT NULL AND t2.cluster_name = t3.store_number
WHERE t1.host_name LIKE :host_name AND t3.store_number LIKE :store_number
ORDER BY t1.host_name;

To clarify, not all hosts have clusters, and not all clusters associate to store numbers. I want to be able to return all rows even if I don't specify a store number in the parameter, but currently it only returns rows that have a store number that is not null. Passing to the parameter as %value%.

Here is what I am getting currently:

+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host_name   | vcenter_name  | connection_state | power_state | cluster_name | store_number | district_number | time_zone | active |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host1       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host1       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+

Here is what I would like to get:

+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host_name   | vcenter_name  | connection_state | power_state | cluster_name | store_number | district_number | time_zone | active |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host1       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host1       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
| host1       | vcenter2      | Connected        | PoweredOn   | NULL         | NULL         | NULL            | NULL      | NULL   |
| host1       | vcenter3      | Connected        | PoweredOn   | cluster3     | NULL         | NULL            | NULL      | NULL   |
| host2       | vcenter3      | Connected        | PoweredOn   | cluster3     | NULL         | NULL            | NULL      | NULL   |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+

Answer 1

Put t3.store_number LIKE :store_number in your JOIN ON criteria rather than the WHERE criteria.

Being in WHERE means the the store_number of NULL won't match the criteria however being in a LEFT JOIN, t3.* are padded in the result when the JOIN criteria isn't met.