+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: How do I inner-join so count doesn't display 1? (/showthread.php?tid=51412)
How do I inner-join so count doesn't display 1? - El Forum - 05-02-2012
[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! ^.^;
How do I inner-join so count doesn't display 1? - El Forum - 05-02-2012
[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
How do I inner-join so count doesn't display 1? - El Forum - 05-03-2012
[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.
How do I inner-join so count doesn't display 1? - El Forum - 05-03-2012
[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?
How do I inner-join so count doesn't display 1? - El Forum - 05-03-2012
[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
