Third join on NULL doesn't return values (Codeigniter)

Question

I have a join statement that doesn't return the wanted values.

The reason is that the third table (cvi) in many cases won't contain any rows that match the fourth table (cmi). When no values are joined, the result does not show the wanted values from tables c and cv.

    /* Tables */
    $this->db->from($this->table_c.' c');
    $this->db->join($this->table_cv.' cv', 'cv.content_id = c.id', 'inner');
    $this->db->join($this->table_cvi.' cvi', 'cvi.version_id = cv.id AND cvi.meta_key = "M"');
    $this->db->join($this->table_cmi.' cmi', 'cmi.id = cvi.meta_value');

I have tried the outer joins but since the table cvi is in between the tables cv and cmi then there are no common values in the two tables.

+------------+
|        c   |
|            |
|      +-----|------+
|      |     |  cv  |
+------------+      | 
       |       +------------+
       |       |    |  cvi  | When this table is empty, the result is empty
       +-------|----+       | I want it to still show result of c and cv
               |      +------------+
               |      |     |  cmi |
               +------------+      |
                      |            |
                      |            |
                      +------------+

Here is an illustration of why there are no shared values. I'm therefore in the search for a way to make a condition that will only join cmi if cvi contains values. Otherwise only make an outer join on cvi and don't include the table cmi

Can you provide some ideas or solution?

EDIT Here are the tables for clarity:

/* Table c */
+------+--------+
| id   | title  |
+------+--------+

/* Table cv */
+------+----------+
| id   | version  |
+------+----------+

/* Table cvi */
+------+------------+-----------+------------+
| id   | version_id | meta_key  | meta_value |
+------+------------+-----------+------------+
/* when meta_key is 'M' then the meta_value will contain the cmi.id which is used for the join (regard it as meta_id) */
/* When this table is empty there won't be data in `cmi` either. When it's empty the join removes the data result that should be present from table `c`. */

/* Table cmi */
+------+-----------+------------+
| id   | item_key  | item_value |
+------+-----------+------------+

Here is a result when there is data in the tables cv and cmi.

Array (
        [0] => stdClass Object
            (
                [id] => 5 /* This is c.id */
                [title] => Content title
                [version_id] => 8 /* This is cv.id */
                [version] => 0
                [meta_key] => M
                [meta_value] => 23 /* (This is the id of the item below, cmi.id) */
                [item_key] => KEY1
                [item_value] => Value
            )
)

Can you show data examples and explain what result set do you expect? Currently you have just `inner join` so you cannot get any result (take a look at your illustration). — zuluk, Jun 23 '17 at 12:08
I'm expecting as a minimum a result per row in table `c` when it has a corresponding row in `cv` (this join is the one not shown in the illustration). So there is an inner join between `c` and `cv`. This inner join is correct and produces the correct result, if there are just available values in all tables. But if there are no values in the third table `cvi` then the link to the fourth table `cmi` is "broken" and it removes the values from the inner join of the `c` and `cv` tables. These need to remain in the result even though there are no values to join from the `cvi` and `cmi` tables. — nicolaib, Jun 23 '17 at 12:51

zuluk · Answer 1 · 2017-06-23T13:17:12.420

0

In my opinion you have to take left joins:

/* Tables */
$this->db->from($this->table_c.' c');
$this->db->join($this->table_cv.' cv', 'cv.content_id = c.id', 'inner');
$this->db->join($this->table_cvi.' cvi', 'cvi.version_id = cv.id AND cvi.meta_key = "M"', 'left');
$this->db->join($this->table_cmi.' cmi', 'cmi.id = cvi.meta_value', 'left');

So you will get empty columns if the join conditions for cvi and cmi not create any result.

Edit after your comment: Maybe EXISTS helps:

$this->db->where("EXISTS(SELECT * FROM cmi)");

Codeigniter subquery exists

edited Jun 23 '17 at 13:17

answered Jun 23 '17 at 13:02

zuluk

1,557
8
29
49

The issue is (as I see it) not whether it is a outer, inner, left or right join, because the values will never be joinable, when there is nothing in the linking table `cvi`. I need to find a solution to test for the results of the `cvi` table and only join `cmi` if there are any results in `cvi`. Like some condition. – nicolaib Jun 23 '17 at 13:14
Maybe this https://stackoverflow.com/questions/25561306/codeigniter-subquery-exists helps. – zuluk Jun 23 '17 at 13:17
I tried this but so far without success. Can you explain in detail how this will solve it. Just because I'm not sure I understand how the sub query will know what data to select given the rest of the query. Thanks. – nicolaib Jun 23 '17 at 15:12
Maybe I did not understand your issue. Do you want to show data from cmi even there is no matching row with cvi? It would be easier when you can show some example data for all 4 tables and 1 table you expect as result. Because with the left joins the data from c and cv is shown even there is no matching key on cvi or cmi. – zuluk Jun 23 '17 at 15:24
No, I want to show data from `c` and `cv` even though there is no data in `cvi` or `cmi`. This doesn't happen in my current join, because of the missing data in `cvi` that acts as a link between `cv` and `cmi`. I have added the tables and a result to the question, so you have a chance to see them. Thanks. Note: If I remove the last join (to table `cmi`), then you are right that the left joins are the answer, but not when its there. I believe I need to make that join conditional, so it only happens when the `cvi` has content. – nicolaib Jun 24 '17 at 18:29

Third join on NULL doesn't return values (Codeigniter)

1 Answers1