The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I'd like to update this so if a comment is made to an entry within the selected category, I can display some additional info related to the comment. This data will be coming from the exp_comments table:
1) {commnet_id}
2) {name} // username of comment author

The above attempt seems to be returning all comments to each entry within the selected category. Not what I want.

I want to do what my initial query is doing, return each entry within a selected category, and if that entry has a comment, pull in the data for {comment_id} and {name}.

This make sense? This is clearly related to how I'm joining the tables and the inclusion of the {comment_id) column in relationship to the DISTINCT statement but I can't seem to figure out how to get around this and at the data from exp_comment without screwing up my original intent.

If I remove c.comment_id and c.name from my SELECT the query performs as I want it to but I don't have the additional info I'm after.

...could you please give a quick rundown on the one-to-many relationships involved here

I'll try my best to explain whats occurring. The second query in my initial post (which includes the LEFT JOIN to my exp_comments table) returns what I need if {c.comment_id} and {c.name} are NOT included in the SELECT.

When included in the SELECT the query returns all comment_id's for each entry in the selected category. See second attached image.

where is the posts table? is there a posts table?

By "posts table" do you mean "comments" or "entry"?

Here is a rundown of my tables. I have:exp_members - includes member info; ie member_id = author_idexp_comments - includes all comment info for each weblog entry (common columns: entry_id; author_id)exp_weblog_titles - includes all entry stats: entry title, entry date, entry_id, recent comment date, etcexp_category_posts - relationship table that assigns a each entry to a category: entry_id to category_idexp_categories - assigns readable category name to category_id

I've attached two screen captures to help further explain.query_1.gif shows the results from my first query and how I'd like the results to look less the two needed columns.

query_2.gif shows the results from my second attempt and shows how each comment for entry is displayed.

if an entry has multiple comments, and you retrieve all of them, then there will be one row in the result set for each comment for each entry

if, instead, what you want is one row per entry, then you need to display the comment data differently -- perhaps concatenate all comments into one value, or not show all the comments for each entry but instead pick just one, etc.

by the way, the purpose of the query is to show all entries for a specific weblog in a specific category

your FROM clause should reflect that

what you have is this --

Code:

FROM exp_members AS m
LEFT JOIN exp_weblog_titles AS t
ON m.member_id = t.author_id
LEFT JOIN exp_category_posts AS cp
ON t.entry_id = cp.entry_id
LEFT JOIN exp_categories AS cats
ON cp.cat_id = cats.cat_id
WHERE t.weblog_id = "6" AND cats.cat_url_title = 'trends'

basically, this says start with the member table, get all entries every member ever wrote, get all categories for each of those entries, and then throw everything away except for entries in this specific weblog in this specific category

instead, your FROM clause should work like this: start with the specified category, get all entries in the specified weblog, and get the authors of those entries

So...with the comments table added my results set strays from what I want in two ways...one, as before, I'm returning every comment for each weblog entry; and two, I've lost the author information as it relates to the (parent) weblog entry.

To eliminate all the extra comments, or maybe better described...only retun the most recent comment couldn't I do this...?

Code:

INNER
JOIN exp_comments AS c
ON t.entry_id = c.entry_id
AND c.comment_date =
(SELECT MAX(comment_date) // only most recent
FROM exp_comments
WHERE entry_id = t.entry_id
AND status = 'open' ) // only open comment

I think this should leave me with just one issue to resolve...the loss of my weblog entry's author information.

Code:

FROM exp_categories AS cats
INNER
JOIN exp_category_posts AS cp
ON cp.cat_id = cats.cat_id
INNER
JOIN exp_weblog_titles AS t
ON t.entry_id = cp.entry_id
AND t.weblog_id = 6
// Does my comments table have to JOIN to my members table? Or, can I JOIN two different table to two separate columns on one table?
INNER
JOIN exp_comments AS c
ON t.entry_id = c.entry_id
AND c.comment_date =
(SELECT MAX(comment_date) // only most recent
FROM exp_comments
WHERE entry_id = t.entry_id
AND status = 'open' ) // only open comment
INNER
JOIN exp_members AS m
ON m.member_id = t.author_id
WHERE cats.cat_url_title = 'trends'

yes, choosing the latest comment per entry is definitely going to solve the "haywire" problem (see your reply to post #2... it looks like you might have thought he was suggesting the last comment ever made on any entry, which of course would not be right)

you're doing the MAX subquery right, too

i didn't understand this --

// Does my comments table have to JOIN to my members table? Or, can I JOIN two different table to two separate columns on one table?

the way joins work, each table that you bring into the join must be tied, by column values, to some previous table or tables