Mysql – Results not returning rows where second parameter should match null

MySQLPHP

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

Best Answer

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.

Related Solutions

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

SQL SP – How to Return One Inactive Record with Active Records

It looks to me like a simple WHERE IsActive = 1 OR specialtyId = @specialtyId should work.

Best Answer

Related Solutions

MySQL – Get Column from Multiple Tables

SQL SP – How to Return One Inactive Record with Active Records

Related Question