SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
-
Jan 25, 2005, 21:52 #1
- 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
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 purposesMatt Daly
-
Jan 26, 2005, 12:07 #2
- 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 + "'
Matt Daly
-
Jan 26, 2005, 19:48 #3
- 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
-
Jan 26, 2005, 20:17 #4
- 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
-
Jan 26, 2005, 23:36 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
yes, that's right
JOIN syntax is preferred, though
-
Jan 28, 2005, 11:22 #6
- 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
-
Jan 28, 2005, 11:50 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Jan 28, 2005, 22:54 #8
- 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
-
Jan 29, 2005, 03:23 #9
Bookmarks