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:
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)
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 purposes