HaLo2FrEeEk Posted March 7, 2007 Share Posted March 7, 2007 I cannot figure it out, it seems perfect to me, but I'm tired right now. Its supposed to get the latest topic_id from ONLY forum 1, which is news, where the title of the post starts with "Weekly Update: ", but it is returning the latest value from the entire database whose title starts with "Weekly Update: ", what am I doing wrong: SELECT post_text FROM phpbb_posts_text WHERE post_id = ( SELECT topic_first_post_id FROM phpbb_topics WHERE topic_id = ( SELECT max(t.topic_id) FROM phpbb_forums f, phpbb_topics t WHERE f.forum_id = '1' AND t.topic_title like 'Weekly Update: %' ) ) Quote Link to comment Share on other sites More sharing options...
btherl Posted March 7, 2007 Share Posted March 7, 2007 I think you might need a condition to join f and t in your inner subquery, like AND f.id = t.id To test it, run the inner subquery on its own and see what you get Quote Link to comment Share on other sites More sharing options...
HaLo2FrEeEk Posted March 8, 2007 Author Share Posted March 8, 2007 When I run the subquery by itself, It still makes the scope the entire forum, this is the subquery: SELECT max( t.topic_id ) FROM phpbb_forums f, phpbb_topics t WHERE f.forum_id =1 AND t.topic_title LIKE 'Weekly Update: %' Quote Link to comment Share on other sites More sharing options...
btherl Posted March 8, 2007 Share Posted March 8, 2007 That's because you haven't added a join condition, like AND f.id = t.id You'll need to use the appropriate column which links f and t. If you're still having trouble, can you post the table definitions? Quote Link to comment Share on other sites More sharing options...
HaLo2FrEeEk Posted March 8, 2007 Author Share Posted March 8, 2007 There is no f.id, there is f.forum_id, and there is t.topic_id, and they are not equal, I want results from forum_id 1, which is news, and it'll be returning a topic id which will be int he 4 and 5 hundreds. It is a default phpbb installation, if you have access to one to look at. What do you mean by table definitions? Quote Link to comment Share on other sites More sharing options...
Guest footballkid4 Posted March 8, 2007 Share Posted March 8, 2007 The subquery should look something like: SELECT max(t.topic_id) FROM phpbb_forums f, phpbb_topics t WHERE t.forum_id=f.forum_id AND f.forum_id =1 AND t.topic_title LIKE 'Weekly Update: %' Quote Link to comment Share on other sites More sharing options...
HaLo2FrEeEk Posted March 8, 2007 Author Share Posted March 8, 2007 HOLY! Ok, it works, thanks, youa re amazing, how does that work though, it makes no sense. Can you please explain, not that I'm complaining, I'd just like to know for any future things like this. Thank you. Quote Link to comment Share on other sites More sharing options...
Guest footballkid4 Posted March 8, 2007 Share Posted March 8, 2007 Basically the t.forum_id=f.forum_id tells SQL to match up the columns in that manner. Without matching those up, you never know how your data will match up. You could have a topic from forum 3 that will show up with data from forum 2...since you never told MySQL to match it up. Basically, MySQL reads that statement and thinks: Take all of the rows from the specified table with the specified WHERE clause, and match them up so that the topic table's forum_id is the same as the forum table's forum_id. This will ensure that all topics in forum 3 are associated with forum 3 data. It's relatively hard to explain, which is why I'm going to end it here. All that matters is that it's a way of telling MySQL where to sync up the data. Quote Link to comment Share on other sites More sharing options...
HaLo2FrEeEk Posted March 8, 2007 Author Share Posted March 8, 2007 I understand completely, that was a good explanation (I have a pretty good head on my shoulders, just sometimes I miss things, and I just got home from work, so I'm kinda tired, and missed something :-\) Anywho, I didn't see that phpbb_topics had a forum_id row in it, otherwise I might have figured it out...maybe...anyways... Thank you very much for your help, it is much appreciated. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.