Welcome Guest, Not a member yet? Register   Sign In
SQL Join
#1

[eluser]iainco[/eluser]
Hey guys, having trouble getting information from my database that represents which feeds have been selected by a user.

Database tables:
user_feed (userID, feedID) (Association between a user and a sports news feed)

feed (ID, sportID, categoryID, title) (Basic information for a sports news feed)

Code:
function fetchFeeds($categoryID)
{
    $this->db->select('feed.ID, feed.title, user_feed.feedID');
    $this->db->where('feed.categoryID', $categoryID);
    $this->db->from('feed');
    $this->db->join('user_feed', 'user_feed.feedID = feed.ID', 'left');

    return $this->db->get()->result_array();
}

The above works, and shows me all feeds that the current logged in user has saved and all feeds they haven't saved (for each category).

This is what I am looking for, but this query also shows me feeds that other users have saved - because I'm not using the current user's ID any where in my query.

Code:
function fetchFeeds($categoryID)
{
    $this->db->select('feed.ID, feed.title, user_feed.feedID');
    $this->db->where(array('feed.categoryID' => $categoryID, 'user_feed.userID' => $this->session->userdata('ID')));
    $this->db->from('feed');
    $this->db->join('user_feed', 'user_feed.feedID = feed.ID', 'left');

    return $this->db->get()->result_array();
}

But when I try this, it shows me only the feeds the current logged in user has saved (and not the feeds he/she hasn't saved)

I know the solution is using the join correctly but having tried all types (left, right, inner, outer, left outer, right outer) and not getting the result I was looking for I thought I'd post here and ask for some help.

Thanks!

Iain


Messages In This Thread
SQL Join - by El Forum - 08-21-2008, 07:35 AM
SQL Join - by El Forum - 08-21-2008, 08:11 AM
SQL Join - by El Forum - 08-21-2008, 08:27 AM
SQL Join - by El Forum - 08-21-2008, 09:55 AM
SQL Join - by El Forum - 08-21-2008, 10:23 AM
SQL Join - by El Forum - 08-21-2008, 10:48 AM
SQL Join - by El Forum - 08-21-2008, 01:03 PM
SQL Join - by El Forum - 08-21-2008, 01:32 PM



Theme © iAndrew 2016 - Forum software by © MyBB