Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    10

    Unanswered: How to join three tables

    Hi All

    I'm having some problems with query that needs to obtain values from two tables which has an m:n relationship. First I'll just lay out the tables so that you can better understand my problem.

    ---------------------
    table Articles

    ArticleID PK
    Heading
    Intro
    Text
    Category
    ---------------------

    ---------------------
    table ImageUsers

    ArticleID PK
    ImageID PK
    Position
    ----------------------

    ----------------------
    table Images

    ImageID PK
    ImageURL
    ----------------------

    As you can see the tables represents articles which can have one or more images associtated with them.

    I need to run a query that will return the following information for all entries in table "Articles":

    Article.ArticleID, Article.Heading, Article.Intro, Article.Text, Article.Category, ImageURL.

    ImageURL should only have a value if the article has an entry in table "ImageUsers" and only if ImageUsers.Position = 'small', if not ImageURL in the recordset should be empty, or contain a null value.

    I would also like to limit the result to articles belonging to a certain category.

    I've tried different variations of joins and subqueries but I just can't seem to come up with a query that will return the correct values.

    All help will be appreciated

    Regards

    Stian

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    what about ...

    SELECT a.articleid, a.heading, a.intro, a.Text, a.categroy, i.imageurl
    FROM articles a, imageusers iu, images i
    WHERE a.articleid = iu.articleid
    AND iu.imageid = i.imageid

    the result: all articles that have at least one image

    Hope this helps

    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Feb 2002
    Posts
    10

    That works but...

    I also need to return those articles that does not have any images at all. I guess I could run a separate query to do this, but I'd rather not.

    Cheers

    Stian

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello again,

    than you need an outer join :

    SELECT a.articleid, a.heading, a.intro, a.Text, a.categroy, i.imageurl
    FROM articles a, imageusers iu, images i
    WHERE a.articleid = iu.articleid(+)
    AND iu.imageid = i.imageid(+)

    This works for Oracle ... Which database are you using ?

    Greetings
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  5. #5
    Join Date
    Feb 2002
    Posts
    10
    Hi Again

    Currently I'm using a MS Access database, but will be moving to mySQL in the near future

    Stian

Posting Permissions

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