Skip to main content
Database Administrators

Return to Question

added 37 characters in body
Source Link
Benjamin Hubbard
Benjamin Hubbard
  • 121
  • 5

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%.

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.

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%.

Added output of what I am getting and what I would like to get. Also changed the parameters to named parameters.
Source Link
Benjamin Hubbard
Benjamin Hubbard
  • 121
  • 5

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 itthe 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.

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   |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+

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, but it only returns rows where it 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 "%" AND t3.store_number LIKE "%"
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.

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.

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   |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
Source Link
Benjamin Hubbard
Benjamin Hubbard
  • 121
  • 5

Results not returning rows where second parameter should match null

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, but it only returns rows where it 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 "%" AND t3.store_number LIKE "%"
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.