SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with SQL query syntax

    I have a table structure with queries that work on website, but would like to export some content out directly from the database. (So its a bit more complicated as it doesn't have a revision details page already pulling through a single record's details.

    The table structure is:

    Table: Revision

    RevisionID
    Revision
    Status
    etc

    1, Delete Museum, 2 etc

    Table: Profiles

    ProfileID
    Profile
    Profile_Category
    Category_Order

    1, Museums, Places of Interest, 2 etc

    Interlinking Table: revisionProfiles

    ProfileID
    RevisionID

    1, 1

    What I'm looking for in my query is all the fields from the Revision table plus the Profile field from the Profiles table, e.g.

    1, Delete Museum, Museums

    WHERE Revision.Status = 2 AND Category_Order = 2

    There are a few Profile_Categories but for this category there will only ever be one Profile for any given Revision, e.g. Museums.

    I've been playing around with it, but not sure of the syntax as there will be joins, and WHERE criteria from two of the tables involved.

    Hope that makes sense.

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT r.RevisionID
         , r.Revision
         , r.Status
         , r.etc
         , p.Profile
      FROM Revisions AS r
    INNER
      JOIN revisionProfiles AS rp
        ON rp.RevisionID = r.RevisionID
    INNER
      JOIN Profiles AS p
        ON p.ProfileID = rp.ProfileID    
       AND p.Category_Order = 2
     WHERE r.Status = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you - that's perfect!


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
  •