• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Join, if?

[eluser]Robert May[/eluser]
Just a quick question: is it possible to use the join command on a query based on whether an entry actually exists?

Here's the query:
function list_entries($number, $offset)
        $this->db->join('authors', 'author_id = news_author_id');
        $this->db->join('translations', 'trans_ref_id = news_id');
        $this->db->order_by('news_id', 'desc');
        $query = $this->db->get('news', $number, $offset);
        return $query->result();

The one that is causing trouble is the translations bit. I want it to only join if the translation actually exists, rather than creating an error.
The only other way I can see to do it is to create blank records for every news article, though I'm probably wrong. Any ideas? Smile

[eluser]Phil Sturgeon[/eluser]
Would a right join not do the trick?

$this->db->join('translations', 'trans_ref_id = news_id', 'right');

That means if you have a news_id, but there is no matching trans_ref_id, it will load NULL for all the fields in translations.

[eluser]Robert May[/eluser]
That worked. Thanks very much for the help, it's hugely appreciated!

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.