[SOLVED] What is wrong with this query?!?!

[HaLo2FrEeEk]

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: %'
    )
  )

[btherl]

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

[HaLo2FrEeEk]

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: %'

[btherl]

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?

[HaLo2FrEeEk]

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?

[Guest footballkid4]

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: %'

[HaLo2FrEeEk]

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.

[Guest footballkid4]

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.

[HaLo2FrEeEk]

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.