title: SQL Joining 3 tables tag system codeigniter
body: <p>I've been trying to come up with a tag system for my posts and I am having troubles getting the values I want. Rather, I can't seem to wrap my head around the logic of the joining of tables. I've tried to find information to help me along but I guess I need someone to really lay down the basics.</p>
  
  <p>Anyway, these are my tables (shortening the posts table);</p>
  
  <pre><code>POSTS
  post_id
  post_title
  post_freetext
  
  POST_TAGS
  post_id
  tag_id
  
  TAGS
  tag_id
  tag_text
  </code></pre>
  
  <p>What I am trying to do is fetching all posts connected to a single tag.</p>
  
  <p>My CodeIgniter code looks like this;</p>
  
  <pre><code>   $this-&gt;db-&gt;select('*');
     $this-&gt;db-&gt;from('posts');
     $this-&gt;db-&gt;join('post_tags', 'post_tags.post_id = posts.post_id' ,'inner');
     $this-&gt;db-&gt;join('tags', 'tags.tag_id = posts.post_id', 'inner');
     $this-&gt;db-&gt;where('tag_text =', $tagid);
     $this-&gt;db-&gt;order_by('posts.post_id', 'desc');
     $q = $this-&gt;db-&gt;get();
  </code></pre>
  
  <p>$tagid in this case is the string (read tag) that I am looking for.</p>
  
  <p>I was successful in joining two tables and fetching the posts but then I realized I would not be able to allow the user to look at all the tags (and thus I needed a separate "tag" table). Can't seem to get this right though.</p>
  
  <p>Any help at all would be very much appreciated and I do realize that this question has probably been answered multiple times - still, can't wrap my head around the logic.</p>