SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Location
    Jersey
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    complex SQL query, best way to simplify it

    i only created the database an hour ago, so it might not be very well set out but i never was big on database design. i included an access relationship view of the relationships i have/want so you can see exactly what i'm looking for.

    i've created a query, and it's rather long and undoubtedly one of the silliest ways of doing it, but i've only ever used JOINS once and that wasn't much, so i wasn't sure where to start with a join for this.

    my aim: articles get posted, stored inside the articles table. each article belongs to a certain "group", say PHP, .NET, SQL etc. each article also has comments people add associated with it. each article again also has the user that posted it. the user who posted it is stored using their ID in the article table. this is where i began to struggle. i have a users table that only has 3 rows, user_id, group_id, and profile_id. auto incrementing user id, a group id linked to a users group table, and a profile id linked to a profile table, the profile table then stores all data associated to that user (can be seen in the access diagram).

    i've managed to link it all together in my query and everything displays as it's supposed to depending on who entered it and the group they entered it into etc. but as i said, the queries not the best, it's probably the worst, and i'm not to familiar with joins or sub queries so i didn't attempt it. if anyone could tidy up the query / give pointers to how i'd go about the joins i'd be greatful.

    here's the access image:


    and here's the query:
    Code:
    SELECT * FROM Articles, Groups_Articles, Users, Profiles_Users WHERE article_id='" + qArticleID +  "' AND Articles.group_id = Groups_Articles.group_id AND Articles.user_id = Users.user_id AND Users.profile_id = Profiles_Users.profile_id
    as you can see the comments part isn't anywhere in that statement it's in a seperate statement associated with a different asp control. (SELECT TOP 10 * FROM Comments_Articles WHERE article_id='" + qArticleID + "' ORDER BY displayorder DESC)

    qArticleID is the articles ID sent through query string and retreived.

    ta for any help

    p.s. table names in the access diagram are different, i just quickly created it for thought purposes
    Matt Daly

  2. #2
    SitePoint Zealot
    Join Date
    May 2004
    Location
    Jersey
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if anyone could get the following in there somehow as well

    Code:
    SELECT AVG(rating) AS AvgRating FROM Ratings_Articles WHERE article_id='" + qArticleID +  "'
    ta
    Matt Daly

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Location
    Jersey
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well i decided to do some reading. and i'm working on it using joins. i've now got it to select the username from the profiles table (using nested joins). here's what i've got so far

    Code:
    "SELECT Subject, Body, postdate, posttime, Profiles_Users.Username " +
    "FROM Articles " +
    "INNER JOIN (Users " +
    "INNER JOIN Profiles_Users " +
    "ON Profiles_Users.profile_id = Users.profile_id) " +
    "ON Users.user_id = Articles.user_id " +
    "WHERE article_id='" + qArticleID +  "'"
    Matt Daly

  4. #4
    SitePoint Zealot
    Join Date
    May 2004
    Location
    Jersey
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i just read that using AND statements like i originally did within the WHERE clause is the same as a JOIN, is that true?

    if so which is the more efficient to use?
    Matt Daly

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, that's right

    JOIN syntax is preferred, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    May 2004
    Location
    Jersey
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    finally got to grips with JOINS, here's my code:

    Code:
    SELECT  Articles.Subject, " +
           "Articles.Body, " +
           "Articles.postdate, " +
           "Articles.posttime, " + 
           "Articles_Groups.gname, " +
           "Staff_Profiles.username " +
    "FROM   Articles " +
         "INNER JOIN Articles_Groups ON Articles.group_id = Articles_Groups.group_id " +
         "INNER JOIN Staff_Accounts ON Articles.user_id = Staff_Accounts.user_id " +
         "INNER JOIN Staff_Profiles ON Staff_Accounts.profile_id = Staff_Profiles.profile_id " +
    "WHERE article_id='" + qArticleID +  "'
    Matt Daly

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    congrats

    a first class query

    if article_id is numeric, the last line should be

    "WHERE article_id= " + qArticleID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    May 2004
    Location
    Jersey
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm. i have a strange problem. i haven't touched the code in anyway at all since i last posted (except the change r937 suggested) and the query's stopping running.

    i played around with it. and the problem is something to do with references to departments. when any reference to the department table is gone the query works fine. i haven't changed any info for either the staff accounts or staff department tables. i can't figure out why it's suddenly stopped working.

    only thing i've changed is the reference to the connection. i changed it (after testing) to a web.config connection key. but i've changed it back to exactly what it was before and the error's still occuring. strange o.O

    anyone have any suggestions?
    Matt Daly

  9. #9
    Non-Member DaveMichaels's Avatar
    Join Date
    Nov 2004
    Location
    US
    Posts
    535
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


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
  •