Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: Join Category Table so that Categories are in the Result on Only

    SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
    Tag.SortOrder, TagCat.name

    FROM Image, JobTag, Tag, Job, Tagcat

    WHERE Tag.TagID = JobTag.TagID
    AND Image.JobID = Job.JobID
    AND Job.JobID = JobTag.JobID
    AND TagCat.TagCatID = Tag.TagCatID

    ORDER BY Image.ImageID, Tag.SortOrder


    I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this? Would I need to create more than one query or do you think this can be one with just one query?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that query already has joins in it -- inner joins

    the query is merely written using the deprecated "comma list" syntax

    the equivalent query using JOIN syntax is --
    Code:
    SELECT DISTINCT 
           Image.ImageID
         , Image.JobID
         , Image.Filename
         , Tag.Name
         , Tag.SortOrder
         , TagCat.name
      FROM Image
    INNER
      JOIN Job
        ON Job.JobID = Image.JobID
    INNER
      JOIN JobTag
        ON JobTag.JobID = Job.JobID
    INNER
      JOIN Tag
        ON Tag.TagID = JobTag.TagID
    INNER
      JOIN Tagcat
        ON TagCat.TagCatID = Tag.TagCatID
    ORDER 
        BY Image.ImageID
         , Tag.SortOrder
    i did not understand what you meant by "only get the category (TagCat.name) one time for each set of tags"

    could you give an example using sample rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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