• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How do I inner-join so count doesn't display 1?

#1
[eluser]emily12[/eluser]
After a lot of research I have found out that the solution to my problem exists with something called 'inner' instead of 'left' join. I had never heard of this before and wondered how this would work?

Code:
function get_featurednews() {
//////// TODO: Guarantee that it cannot be featured more than once
   $this->db->select('b.user_id, b.username, p.narticleid, COUNT(*) AS votes, v.aid, v.vdate, p.news_type, p.full_url, p.l_title, p.author, p.l_desc, p.s_desc, p.m_thumb');
   $this->db->from('table_news_articles AS p');
   $this->db->join('table_news_votes AS v', 'v.aid = p.narticleid', 'inner');
   $this->db->join('phpbb_users AS b', 'p.author = b.user_id', 'left');
   $this->db->group_by('p.narticleid');
   $this->db->where('p.time >=', date('Y-m-d H:i:s', time() - 86400 * 7)); // Past Week
   $this->db->limit(8);

  $query = $this->db->get();
        return $query;
}

I'm more or less wondering how it works with an example so I could know for the future not to repeat this mistake and to better understand join. The given function won't work with 'inner' but it does with 'left' however $votes will display 1 as a minimum (see count) even though there's 0 vote results for that article.

Thank you! ^.^;

#2
[eluser]JoostV[/eluser]
JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

#3
[eluser]emily12[/eluser]
Right, but my question is how do you use inner join given my example? There's also an outer join which I know nothing about.

#4
[eluser]GrahamDj28[/eluser]
Hi,

It all depends on what you want to do and what the table relations are.

So... What is it you want to do?

#5
[eluser]emily12[/eluser]
[quote author="GrahamDj28" date="1336077671"]Hi,

It all depends on what you want to do and what the table relations are.

So... What is it you want to do?[/quote]

Please look at the first post, I explain that the vote count will always be 1 even though there can be 0 entries for that article. I've heard that using an inner join is the solution to this. I COULD automatically create a new entry that votes the article by the submitter but I want to know how this works instead of cheating Tongue


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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