complex SQL query, best way to simplify it

[matt-daly]

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:

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]

if anyone could get the following in there somehow as well

SELECT AVG(rating) AS AvgRating FROM Ratings_Articles WHERE article_id='" + qArticleID +  "'

ta

[matt-daly]

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

"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]

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?

[r937]

yes, that's right

JOIN syntax is preferred, though

[matt-daly]

finally got to grips with JOINS, here's my 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 +  "'

[r937]

congrats

a first class query

if article_id is numeric, the last line should be

"WHERE article_id= " + qArticleID

[matt-daly]

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?

[DaveMichaels]

Trying to build a team behind that technology would be a staffing nightmare.