SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: SQL JOIN help

  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy SQL JOIN help

    I am writing an application to retrieve reviews from a database and have four tables that I'm running a query on:

    user - which contains all of the registered users details with a primary key of "ID"

    reviews - which contains the title, text etc of the review, plus a foreign key that each represents a format in the "formatID" field and category in the "catID" field.

    formats - which contains a list of the formats' names in "format" and the key in "ID"

    categories -which contains a list of categories names in "cat" and the key in "ID"

    My problem is this - a user can write a review, but the category and format fields (selected via a list box) are entirely optional, in which case the category or the format field may be blank.
    My original query to select all of the details worked fine in most cases:
    Code:
     
    SELECT reviews.authorID AS authortag, reviews.title, reviews.price, 
    reviews.publisher, reviews.reviewtext, reviews.rating, UNIX_TIMESTAMP
    (reviews.date) AS date, user.fullname, categories.cat, formats.format 
    FROM reviews, user, categories, formats WHERE 
    reviews.authorID=user.ID AND reviews.catID=categories.ID AND reviews.formatID=formats.ID AND reviews.ID=$revID
    (where $revID is the primary key of the review)

    Unfortunately, if that particular review didn't have a set value for the category or format, the query returned nothing, even though the review exists. My thoughts then where that I needed to use a LEFT JOIN to return the review in case the cat or format was unspecified.

    My convoluted attempt at an amended query with LEFT JOINS doesn't work:
    Code:
     
    SELECT reviews.authorID AS authortag, 
    reviews.title, reviews.price, reviews.publisher, 
    reviews.reviewtext, reviews.rating, UNIX_TIMESTAMP(reviews.date) AS date, 
    user.fullname, categories.cat, formats.format 
    FROM reviews INNER JOIN user ON reviews.authorID=user.ID AND reviews.ID=$revID,
    reviews LEFT JOIN categories ON reviews.catID=categories.ID
    LEFT JOIN formats ON reviews.formatID=formats.ID;
    can anyone help me out with this at all? I wanted to do it all in one query, or do i need to do three seperate queries - one to fetch the review, then two to retrieve the names of the category and formats?

    i'm quite desperate to crack this, so many thanks in advance.

  2. #2
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you add a row in format and category that represents blank, and has an ID of say 0, and in the reviews, if a review has no format or category, those columns will have an id of 0.
    Work smarter, not harder. -Scrooge McDuck

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    robo, no offence, but "dummy" rows in any table are hardly ever a good solution

    dalek, you almost had it -- at least, you got the LEFT OUTER idea, which is the way to do this, but just messed up the inner join somehow

    here's yours --

    Code:
      FROM reviews 
    INNER
      JOIN user 
        ON reviews.authorID=user.ID 
       AND reviews.ID=$revID
         , 
           reviews
    LEFT
      JOIN categories 
        ON reviews.catID=categories.ID
    LEFT
      JOIN formats 
        ON reviews.formatID=formats.ID
    the ON clause may not include a condition on the leftmost table, and you've inadvertently repeated the left table using comma table list syntax

    try this --

    Code:
      FROM reviews 
    INNER
      JOIN user 
        ON reviews.authorID=user.ID 
    LEFT
      JOIN categories 
        ON reviews.catID=categories.ID
    LEFT
      JOIN formats 
        ON reviews.formatID=formats.ID
     WHERE reviews.ID=$revID
    rudy
    http://r937.com/

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    many thanks rudy...i will try that out when i get home!


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
  •