2

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
            )
)
2
  • 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). Commented Jun 23, 2017 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. Commented Jun 23, 2017 at 12:51

1 Answer 1

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

Sign up to request clarification or add additional context in comments.

5 Comments

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.