mysql - How to return null values in left join but exclude it when returning specific value with no null value

Question

So I have this table:

history_table

+------------+----------+----------+--------+---------+--------------+--------------+------+---------+
| history_id | TDNO     | titleNO  | lotNO  | area    | encumbrances | assess_value | EFF  | memo_id |
+------------+----------+----------+--------+---------+--------------+--------------+------+---------+
|        145 | F-111111 | T-00000  | LOTF1  | 500 SQM | NONE         |     12331.13 | 2016 | 415     |
|        146 | F-000000 | T-000000 | LOT F0 | 1 HA    | NONE         |       123.23 | 2015 | 412     |
|        147 | E-000000 | T-00000  | LOTE0  | 500 SQM | NONE         |     13123.12 | 1994 | 413     |
+------------+----------+----------+--------+---------+--------------+--------------+------+---------+

memoranda

+---------+--------------+
| memo_id | memo_string  |
+---------+--------------+
|       0 |              |
|     412 | TEST.        |
|     413 | TEST2.       |
|     415 | Test3.       |
+---------+--------------+

td_ownersinfo

  TD_NO     owner           location  transaction  
  --------  --------------  --------  -------------
  F-000000  name1           SAN JOSE  TRANSFER     
  F-111111  name2           LAS VEGAS WALK-IN 

So I have this query:

SELECT 
  history_table.`history_id`,
  history_table.`TDNO` AS 'TAX DECLARATION NO',
  history_table.`titleNO` AS 'TITLE NO',
  history_table.`lotNO` AS 'LOT NO',
  history_table.`area` AS 'AREA',
  history_table.`encumbrances` AS 'ENCUMBRANCES',
  FORMAT(history_table.`assess_value`, 2) AS 'ASSESS VALUE',
  history_table.`EFF`,
  history_table.`memo_id`,
  memoranda.`memo_string` AS MEMORANDA, 
  TD_ownersinfo.`owner`,
  TD_ownersinfo.`location`,
  TD_ownersinfo.`transaction`
FROM
  history_table 
  INNER JOIN memoranda 
    ON memoranda.memo_id = history_table.memo_id 
  LEFT JOIN td_ownersinfo 
    ON history_table.TDNO = td_ownersinfo.TD_NO
WHERE history_table.TDNO LIKE '%%' 
  AND history_table.titleNO LIKE '%%' 
  AND td_ownersinfo.owner LIKE '%%' 
  AND td_ownersinfo.transaction LIKE '%%' 
  AND td_ownersinfo.location LIKE '%%' 
  OR TD_ownersinfo.`TD_NO` IS NULL 
ORDER BY history_table.history_id 

And the result is like this:

history_id  TAX DECLARATION NO  TITLE NO  LOT NO  AREA     ENCUMBRANCES  ASSESS VALUE  EFF     memo_id  MEMORANDA            owner           location  transaction  
----------  ------------------  --------  ------  -------  ------------  ------------  ------  -------  -------------------  --------------  --------  -------------
       145  F-111111            T-00000   LOTF1   500 SQM  NONE          12,331.13     2016    415      TEST3.               name2           LAS VEGAS  WALK-IN      
       146  F-000000            T-000000  LOT F0  1 HA     NONE          123.23        2015    412      TEST.                name1           SAN JOSE  TRANSFER     
       147  E-000000            T-00000   LOTE0   500 SQM  NONE          13,123.12     1994    413      TEST2.               (NULL)          (NULL)    (NULL)       

But when I try to put value in history_table.TDNO LIKE '%%' the TDNO that has null value in td_ownersinfo table will still be displayed. For example, I will put value in history_table.TDNO in the same query: history_table.TDNO LIKE '%F-111111%'. The result will be like this:

history_id  TAX DECLARATION NO  TITLE NO  LOT NO  AREA     ENCUMBRANCES  ASSESS VALUE  EFF     memo_id  MEMORANDA   owner           location  transaction  
----------  ------------------  --------  ------  -------  ------------  ------------  ------  -------  ----------  --------------  --------  -------------
       145  F-111111            T-00000   LOTF1   500 SQM  NONE          12,331.13     2016    415      TEST3.      name2           LAS VEGAS  WALK-IN      
       147  E-000000            T-00000   LOTE0   500 SQM  NONE          13,123.12     1994    413      TEST2.      (NULL)          (NULL)    (NULL)       

I know the problem is in the WHERE clause where I use OR in OR TD_ownersinfo.TD_NO IS NULL, is there any way to solve this?

Answer 1

This is a complicated problem. Basically you only want to return the rows which have NULL values in td_ownership when you are not making a comparison against a test value on one of the other fields. Here's a way you can do that using variables for the query strings (SQLFiddle):

SET @tdno = '%%';
SET @titleno = '%%';
SET @owner = '%%';
SET @transaction = '%%';
SET @location = '%%';
SELECT 
  history_table.`history_id`,
  history_table.`TDNO` AS 'TAX DECLARATION NO',
  history_table.`titleNO` AS 'TITLE NO',
  history_table.`lotNO` AS 'LOT NO',
  history_table.`area` AS 'AREA',
  history_table.`encumbrances` AS 'ENCUMBRANCES',
  FORMAT(history_table.`assess_value`, 2) AS 'ASSESS VALUE',
  history_table.`EFF`,
  history_table.`memo_id`,
  memoranda.`memo_string` AS MEMORANDA, 
  TD_ownersinfo.`owner`,
  TD_ownersinfo.`location`,
  TD_ownersinfo.`transaction`
FROM
  history_table 
  INNER JOIN memoranda 
    ON memoranda.memo_id = history_table.memo_id 
  LEFT JOIN td_ownersinfo 
    ON history_table.TDNO = td_ownersinfo.TD_NO
WHERE history_table.TDNO LIKE @tdno
  AND history_table.titleNO LIKE @titleno
  AND (td_ownersinfo.owner LIKE @owner OR @owner='%%' AND td_ownersinfo.TD_NO IS NULL)
  AND (td_ownersinfo.transaction LIKE @transaction OR @transaction='%%' AND td_ownersinfo.TD_NO IS NULL) 
  AND (td_ownersinfo.location LIKE @location OR @location='%%' AND td_ownersinfo.TD_NO IS NULL)
  OR 
  td_ownersinfo.TD_NO IS NULL 
  AND CONCAT(@tdno,@titleno,@owner,@transaction,@location) = '%%%%%%%%%%'
ORDER BY history_table.history_id 

It checks for what is effectively a "fetch me all data" query by checking if all the variable strings are '%%' (so the CONCAT of all 5 of them is '%%%%%%%%%%') or if the variable strings associated with the td_ownersinfo table are '%%' and only in that case allows td_ownersinfo.TD_NO to be NULL. To check for a specific owner, you would change @owner to be (for example)

SET @owner='%name2%';

If you were going to be making this query up in a language such as PHP, you wouldn't use the MySQL variables but would do something like:

$tdno = '%%';
$titleno = '%%';
$owner = '%%';
$transaction = '%%';
$location = '%%';
$sql = "SELECT 
  history_table.`history_id`,
  history_table.`TDNO` AS 'TAX DECLARATION NO',
  history_table.`titleNO` AS 'TITLE NO',
  history_table.`lotNO` AS 'LOT NO',
  history_table.`area` AS 'AREA',
  history_table.`encumbrances` AS 'ENCUMBRANCES',
  FORMAT(history_table.`assess_value`, 2) AS 'ASSESS VALUE',
  history_table.`EFF`,
  history_table.`memo_id`,
  memoranda.`memo_string` AS MEMORANDA, 
  TD_ownersinfo.`owner`,
  TD_ownersinfo.`location`,
  TD_ownersinfo.`transaction`
FROM
  history_table 
  INNER JOIN memoranda 
    ON memoranda.memo_id = history_table.memo_id 
  LEFT JOIN td_ownersinfo 
    ON history_table.TDNO = td_ownersinfo.TD_NO
WHERE history_table.TDNO LIKE '$tdno'
  AND history_table.titleNO LIKE '$titleno'
  AND (td_ownersinfo.owner LIKE '$owner' OR '$owner'='%%' AND td_ownersinfo.TD_NO IS NULL)
  AND (td_ownersinfo.transaction LIKE '$transaction' OR '$transaction'='%%' AND td_ownersinfo.TD_NO IS NULL) 
  AND (td_ownersinfo.location LIKE '$location' OR '$location'='%%' AND td_ownersinfo.TD_NO IS NULL)
  OR 
  td_ownersinfo.TD_NO IS NULL 
  AND '$tdno$titleno$owner$transaction$location' = '%%%%%%%%%%'
ORDER BY history_table.history_id";
// use the appropriate db command here...
$result = $conn->query($sql);