subselect or join

SirEvenhand

Okay I know the answer is probably very simple but I just cant seem to figure it out tonight.

What I need to do is get the event that a member has completed.

SELECT t1.Ach_ID, t1.Ach_UserName, t1.Ach_TypeID, t2.AT_Type, t2.AT_Detail, t2.AT_Image, t2.AT_Detail_2, t2.AT_Detail_3, t1.Ach_Time, t1.Ach_Hash, t1.Ach_Expire FROM csv6_network_achievement AS t1, csv6_network_award_types AS t2 WHERE t1.Ach_UserID = '$profileid' AND t1.Ach_TypeID = t2.AT_ID ORDER BY t1.Ach_Time DESC LIMIT 0 , 15

The select above is in production now and is not broken.

What I want to do is allow my members to make commentsabout each event if they want. Just like facebook users do.

To make this work I am adding a comment table that will use the event ID for the relationship.

What would be a good way to get the comments for each event to list for each post?

I can do a select in the loop for each event but that will just increase the number of queries for each page. Any ideas?

Crowly

Some linebreaks in your sql makes it a bit more readable 😉 Also updated it to use joins, instead of the old way with the join in WHERE

SELECT t1.Ach_ID, t1.Ach_UserName, t1.Ach_TypeID, 
       t1.Ach_Time, t1.Ach_Hash, t1.Ach_Expire,
       t2.AT_Type, t2.AT_Detail, t2.AT_Image, t2.AT_Detail_2, t2.AT_Detail_3
FROM csv6_network_achievement AS t1 
INNER JOIN csv6_network_award_types AS t2 ON t1.Ach_TypeID = t2.AT_ID
WHERE t1.Ach_UserID = '$profileid'
ORDER BY t1.Ach_Time DESC 
LIMIT 0 , 1

As for the comments, you add another to this sql, just add a INNER JOIN comments_table AS t3 ON t3.fk_event_id=t1.event_id and drop LIMIT 0,1, but then you will get the data from t1 and t2 for every comment, maybe not the best with if there are alot of comments. I think i would just add a 2nd sql to list the comments of the current event.