homepage Welcome to WebmasterWorld Guest from 54.166.111.111
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Need help with a join
andyd273




msg:3521904
 8:03 pm on Dec 6, 2007 (gmt 0)

I have a simple forum with two tables, MemberThreads and MemberPosts.

I want to grab a list of all of the threads and sort them by the latest post.

SELECT Distinct MemberThreads.ThreadID, MemberThreads.ForumID, MemberThreads.username, MemberThreads.Title, MemberThreads.DatePosted as ThreadDate FROM MemberThreads INNER JOIN MemberPosts ON MemberThreads.ThreadID = MemberPosts.ThreadID WHERE MemberThreads.ForumID = 2 ORDER BY MemberPosts.DatePosted Desc

However, when I did this, it doesn't sort them at all, and just displays them in the order that threads are put in.

If I include MemberPosts.DatePosted in the select it will sort them, but then the list will show threads multiple times, which really wont work.

Is there a way to make this happen in MySQL without some really complicated loops and sorts?

 

tonyaly




msg:3523941
 5:50 am on Dec 10, 2007 (gmt 0)

without testing on your tables it's hard to say for certain that this will work...but try:
select distinct
mt.threadid
,mt.forumid
,mt.username
,mt.title
,mt.dateposted as threaddate
,(
select max(mp.dateposted)
from memberposts mp
where
mp.threadid = mt.threadid) as memberposts_dateposted
from
memberthreads mt
where
mt.forumid = 2
order by
memberposts_dateposted desc

andyd273




msg:3524993
 2:08 pm on Dec 11, 2007 (gmt 0)

SELECT Distinct
MemberThreads.ThreadID,
MemberThreads.ForumID,
MemberThreads.username,
MemberThreads.Title,
MemberThreads.DatePosted as ThreadDate,
(Select max(MemberPosts.DatePosted)
FROM
MemberPosts
WHERE
MemberPosts.ThreadID = MemberThreads.ThreadID) as memberposts_dateposted
FROM
MemberThreads
WHERE
MemberThreads.ForumID = 2
ORDER BY memberposts_dateposted Desc

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Select max(MemberPosts.DatePosted)
FROM
MemberPosts
WHERE

but I can't figure out exactly where the error is...

tonyaly




msg:3525334
 8:05 pm on Dec 11, 2007 (gmt 0)

I'm not sure if it makes a huge difference, but did you try the query I sent with the table abbreviations (mp and mt)? Also, what version of mysql do you have?

andyd273




msg:3525404
 9:32 pm on Dec 11, 2007 (gmt 0)

Yeah, I tried it with those abbreviations... then removed them just to make sure that wasn't the problem.

we're using MySQL 4.0.27

I just need to figure out where the syntax is causing an error...

If only I could get it to give me a more verbose response about exactly what is wrong

tonyaly




msg:3525431
 10:18 pm on Dec 11, 2007 (gmt 0)

ok - let's try:

select
distinct
mt.threadid
,mt.forumid
,mt.username
,mt.title
,mt.dateposted as threaddate
,(
select mp.dateposted
from memberposts mp
where
mp.threadid = mt.threadid
order by mp.dateposted desc
limit 1) as memberposts_dateposted
from
memberthreads mt
where
mt.forumid = 2
order by
memberposts_dateposted desc

andyd273




msg:3525914
 3:27 pm on Dec 12, 2007 (gmt 0)

Hmm, still no luck.

here is the table structures in case it will help any:

CREATE TABLE MemberThreads (
ThreadID int(10) NOT NULL auto_increment,
ForumID int(10) NOT NULL default '0',
username varchar(30) NOT NULL default '',
Title varchar(250) NOT NULL default '',
DatePosted datetime NOT NULL default '0000-00-00 00:00:00',
LastPostDate date NOT NULL default '0000-00-00',
PRIMARY KEY (ThreadID)
) TYPE=MyISAM;

CREATE TABLE MemberPosts (
PostID int(10) NOT NULL auto_increment,
ForumID int(10) NOT NULL default '0',
ThreadID int(10) NOT NULL default '0',
username varchar(30) NOT NULL default '',
Title varchar(250) NOT NULL default '',
PageText mediumtext NOT NULL,
DatePosted datetime NOT NULL default '0000-00-00 00:00:00',
IPAddress varchar(15) NOT NULL default '',
Monitor tinyint(1) NOT NULL default '0',
PRIMARY KEY (PostID)
) TYPE=MyISAM;

And a few lines:

INSERT INTO MemberThreads VALUES (1, 1, 'Admin', 'Hello Members', '2007-09-19 11:15:32', '2007-12-03');
INSERT INTO MemberThreads VALUES (2, 15, 'warrantygrl', 'Any help is appreciated...', '2007-09-19 14:32:21', '0000-00-00');
INSERT INTO MemberThreads VALUES (3, 2, 'MARK8', 'Customer Concern Not Duplicated', '2007-09-19 14:40:30', '2007-11-13');

INSERT INTO MemberPosts VALUES (1, 1, 1, 'Admin', 'Hello Members', 'Welcome to our new Community Forum designed exclusively for members!\r\n\r\nWe are excited about the new forum but we need your help to get it started! Please select one of the new forum categories to ask questions, post comments, and share ideas with other professionals across the nation. A team member or another community member will likely respond. \r\n\r\nThe old forum will remain available until the postings there age out.\r\n\r\nEnjoy, and let us know how you like it!\r\n', '2007-09-19 11:15:32', '0', 1);
INSERT INTO MemberPosts VALUES (2, 15, 2, 'warrantygrl', 'Any help is appreciated...', 'I am relatively new to the whole admin thing. I am trying to find any info that might be of interest to me to help me learn the most I can about my position here. Im very open to whatever is available to me. Anybody know of books, websites, other forums, CDs, tapes, articles, ANYTHING I can get my hands on? Please help me out. Thanks a lot!\r\n', '2007-09-19 14:32:31', '0', 0);
INSERT INTO MemberPosts VALUES (3, 15, 2, 'Kristen', '', ' The workshop is offered in many locations across the United States throughout the year. Please click on the link below to obtain more information!\r\nIn addition to the reference materials you currently have, be sure to access daily for the latest in warranty bulletins, recalls and other timely information.', '2007-09-19 14:35:45', '0', 0);
INSERT INTO MemberPosts VALUES (4, 15, 2, 'Mark8', '', 'I attended a workshop last year, and I highly reccomend going if you haven''t. I had only been a Admin. for a couple months and learned alot from it. We had some people in class who had been WA''s for years and they commented that they learned alot from it as well.\r\nI use the book we got in class regularly to re-check myself or look up info. I call it my "Admin Bible" \r\n-- It''s concise and so much easier to look up information in than the P&P. I even use little sticky tabs to easily reference the information I use regularly - i.e. add times, sublet info, etc.\r\n', '2007-09-19 14:37:27', '0', 0);
INSERT INTO MemberPosts VALUES (5, 2, 3, 'MARK8', 'Customer Concern Not Duplicated', 'Just wondered how many actually use the Customer Concern Not Duplicated labor op''s. \r\nI was at our area Administrators Club meeting and some of the other admin''s.', '2007-09-19 14:40:30', '0', 0);
INSERT INTO MemberPosts VALUES (6, 2, 3, 'RANDI-N', '', 'We don''t use it often, but if my tech has spent a considerable amount of time trying to find a problem and still comes up with nothing, then we will use it.', '2007-09-19 14:41:14', '0', 0);

Thanks for the time you've spent already

tonyaly




msg:3526048
 6:02 pm on Dec 12, 2007 (gmt 0)

Is there any chance you can upgrade to MySQL 5?

andyd273




msg:3526077
 6:41 pm on Dec 12, 2007 (gmt 0)

I can request our ISP for it... sadly out of my hands.
Will it make a difference?

tonyaly




msg:3526099
 6:56 pm on Dec 12, 2007 (gmt 0)

I believe the problem is that 4.0 doesn't allow subselects. An upgrade should do the trick. The alternative is to resort to the looping solution you mentioned in your first post.

andyd273




msg:3526109
 7:10 pm on Dec 12, 2007 (gmt 0)

well, I'll have to look into that. Thanks for your help!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved