SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Orphaned posts not showing

    Hello all,

    I have a query that selects all blog posts from a table, joining authorID and categoryID with their appropriate name value from their own tables (i.e. blog_posts, users, categories). However, I'm running into a problem now - if any of my posts are orphaned, i.e. the category that they were assigned to originally no longer exists, the below query will no display them in the result set. I know I should tackle this issue by fixing orphaned posts, but I was also wondering if there was anyway to modify the query such that the orphaned posts would display anyways.

    Code:
    SELECT
         post.postID, post.title, post.datestamp,
         user.name,
         cat.title
    FROM blog_posts post
    INNER JOIN users user
         ON post.author = user.userID
    INNER JOIN blog_categories cat
         ON post.category = cat.catID
    ORDER BY post.postID DESC
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Use a LEFT OUTER JOIN on the categories table, instead of an INNER JOIN

  3. #3
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, works like a charm.
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •