两次加入同一张桌子

Using Codeigniter active record class I am joining my tables in the model like so:

  $subquery =
            $this->db
            ->from('houseconstructiontypelist')
            ->where('HouseFk', $id)
            ->where('discount_id', '0')
            ->get_compiled_select();

  $query = 
            $this->constructiontype_model
            ->select('*, constructiontype.PkID AS ConstructionTypeID')
            ->join('('.$subquery.') t2', 'constructiontype.PkID = t2.ConstructionTypeFk', 'left outer')
            ->find_all();

  return $query;

And because there is no matching row in the second table with 'discount_id' = 0 I get my results:

array (size=5)
  0 => 
    object(stdClass)[50]
      public 'PkID' => null
      public 'Name' => string 'Standard' (length=8)
      public 'Descr' => string 'Standard' (length=8)
      public 'HouseFk' => null
      public 'ConstructionTypeFk' => null
      public 'Price' => null
      public 'Discount' => null
      public 'Subvention' => null
      public 'discount_id' => null
      public 'ConstructionTypeID' => string '1' (length=1)
  1 => 
    object(stdClass)[51]
      public 'PkID' => null
      public 'Name' => string 'Prestige' (length=8)
      public 'Descr' => string 'Prestige' (length=8)
      public 'HouseFk' => null
      public 'ConstructionTypeFk' => null
      public 'Price' => null
      public 'Discount' => null
      public 'Subvention' => null
      public 'discount_id' => null
      public 'ConstructionTypeID' => string '2' (length=1)

I got all the results from the first table (constructiontype) regardless if there are any matching results in the second table (houseconstructiontypelist), fine everything works. And there will always be only ONE! matching result in the second table, notice the ->where('discount_id', '0') part of the subquery.

Now what if I also want to join other results from my second table ->where_not_in('discount_id', '0')? But I also want to keep the result the first query gave me! I want to get this:

array (size=5)
  0 => 
    object(stdClass)[50]
      public 'PkID' => null
      public 'Name' => string 'Standard' (length=8)
      public 'Descr' => string 'Standard' (length=8)
      public 'HouseFk' => null
      public 'ConstructionTypeFk' => null
      public 'Price' => null
      public 'Discount' => null
      public 'Subvention' => null
      public 'discount_id' => null
      public 'ConstructionTypeID' => string '1' (length=1)
  1 => 
    object(stdClass)[51]
      public 'PkID' => string '610' (length=3)
      public 'Name' => string 'Standard' (length=8)
      public 'Descr' => string 'Standard' (length=8)
      public 'HouseFk' => string '135' (length=3)
      public 'ConstructionTypeFk' => string '1' (length=1)
      public 'Price' => string '4564' (length=4)
      public 'Discount' => string '0' (length=1)
      public 'Subvention' => string '0' (length=1)
      public 'discount_id' => string '3' (length=1)
      public 'ConstructionTypeID' => string '1' (length=1)
  2 => 
    object(stdClass)[51]
      public 'PkID' => null
      public 'Name' => string 'Prestige' (length=8)
      public 'Descr' => string 'Prestige' (length=8)
      public 'HouseFk' => null
      public 'ConstructionTypeFk' => null
      public 'Price' => null
      public 'Discount' => null
      public 'Subvention' => null
      public 'discount_id' => null
      public 'ConstructionTypeID' => string '2' (length=1)

Second element of array (1) is now populated with values because there is a match in the second table, where first element (0) has null values because like I said there is still no match! But I need those null values returned.

I tried with a second subquery (with ->where_not_in('discount_id', '0')) and another join part of the sql:

    $subquery =
            $this->db
            ->from('houseconstructiontypelist')
            ->where('HouseFk', $id)
            ->where('discount_id', '0')
            ->get_compiled_select();

    $subquery2 =
            $this->db
            ->from('houseconstructiontypelist')
            ->where('HouseFk', $id)
            ->where_not_in('discount_id', '0')
            ->get_compiled_select();

    $query = 
            $this->constructiontype_model
            ->select('*, constructiontype.PkID AS ConstructionTypeID')
            ->join('('.$subquery.') t2', 'constructiontype.PkID = t2.ConstructionTypeFk', 'left outer')
            ->join('('.$subquery2.') t3', 'constructiontype.PkID = t3.ConstructionTypeFk', 'left outer')
            ->find_all();

But that only gave me results with populated values, because it found the match and it completely ignores the first join call with subquery ->where('discount_id', '0'):

  0 => 
    object(stdClass)[51]
      public 'PkID' => string '610' (length=3)
      public 'Name' => string 'Standard' (length=8)
      public 'Descr' => string 'Standard' (length=8)
      public 'HouseFk' => string '135' (length=3)
      public 'ConstructionTypeFk' => string '1' (length=1)
      public 'Price' => string '4564' (length=4)
      public 'Discount' => string '0' (length=1)
      public 'Subvention' => string '0' (length=1)
      public 'discount_id' => string '3' (length=1)
      public 'ConstructionTypeID' => string '1' (length=1)
  1 => 
    object(stdClass)[51]
      public 'PkID' => null
      public 'Name' => string 'Prestige' (length=8)
      public 'Descr' => string 'Prestige' (length=8)
      public 'HouseFk' => null
      public 'ConstructionTypeFk' => null
      public 'Price' => null
      public 'Discount' => null
      public 'Subvention' => null
      public 'discount_id' => null
      public 'ConstructionTypeID' => string '2' (length=1)