• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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