• Registered: 2005-11-12
  • Posts: 10

Topic: SQL - Select

Hello,

I got the SQL error "#1064 - You have an error in your SQL syntax near 'SELECT id FROM pun_posts WHERE topic_id = 1) AND t.moved_to IS NULL AND f.id" for the following statement.

SELECT t.id, t.poster, t.subject, t.num_replies, p.message, p.posted, p.id
FROM pun_topics AS t
INNER JOIN pun_forums AS f ON f.id = t.forum_id
INNER JOIN pun_posts AS p ON p.topic_id = t.id
LEFT JOIN pun_forum_perms AS fp ON ( fp.forum_id = f.id
AND fp.group_id =3 )
WHERE (
fp.read_forum IS NULL
OR fp.read_forum =1
)
AND p.id
IN (
SELECT id
FROM pun_posts
WHERE topic_id =1
)
AND t.moved_to IS NULL
AND f.id
IN ( 1 )
ORDER BY t.last_post DESC
LIMIT 15

Any ideas? Please advice.

Regards,

gchoyy

  • Connorhd
  • Connorhd
  • Former PunBB Developer
  • Offline
  • From: Leeds, UK
  • Registered: 2004-06-13
  • Posts: 4,195

Re: SQL - Select

Do you have a new enough version of mysql to use subselects?

And what are you trying to do?

remove

p.id
IN (
SELECT id
FROM pun_posts
WHERE topic_id =1
)

and put

p.topic_id = 1

i think

FluxBB.
Connorhd.co.uk.

Connorhd's Website

  • Registered: 2005-11-12
  • Posts: 10

Re: SQL - Select

It works, but I want to show the first 15 topics with the message body (no replied post) for that forum.

Any ideals?

  • Connorhd
  • Connorhd
  • Former PunBB Developer
  • Offline
  • From: Leeds, UK
  • Registered: 2004-06-13
  • Posts: 4,195

Re: SQL - Select

have a look at the queries in extern.php?

FluxBB.
Connorhd.co.uk.

Connorhd's Website

  • Registered: 2005-11-12
  • Posts: 10

Re: SQL - Select

Yes, I modify from extern.php.

BTW, do you think this is correct way to retrieve the first 15 topics with the message body (no replied post) for a forum:

SELECT t.id, t.poster, t.subject, t.num_replies, p.message, p.posted, p.id
FROM pun_topics AS t
INNER JOIN pun_forums AS f ON f.id = t.forum_id
INNER JOIN pun_posts AS p ON p.topic_id = t.id
LEFT JOIN pun_forum_perms AS fp ON ( fp.forum_id = f.id
AND fp.group_id =3 )
WHERE (
fp.read_forum IS NULL
OR fp.read_forum =1
)
AND t.moved_to IS NULL
AND f.id
IN ( 1 )
group by p.topic_id
ORDER BY t.last_post DESC
LIMIT 15

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: SQL - Select

There is no guarantee you will get the message body of the first post by doing that. You're just telling MySQL to fetch _a_ message body from the posts table where the topic_id is matches that of the topic. You're not specifying which. I believe you would have to do a subselect in order to get the expected result.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website

  • Connorhd
  • Connorhd
  • Former PunBB Developer
  • Offline
  • From: Leeds, UK
  • Registered: 2004-06-13
  • Posts: 4,195

Re: SQL - Select

can't you also order by post id asc?

FluxBB.
Connorhd.co.uk.

Connorhd's Website

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: SQL - Select

No, because ORDER BY is evaluated after GROUP BY.

http://dev.mysql.com/doc/refman/4.1/en/ … p-row.html

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website

  • Registered: 2005-11-12
  • Posts: 10

Re: SQL - Select

How about this?

SELECT t.id, t.poster, t.subject, t.num_replies, p.message, p.posted, t.posted, p.id
FROM pun_topics AS t
INNER JOIN pun_forums AS f ON f.id = t.forum_id
INNER JOIN pun_posts AS p ON p.topic_id = t.id
LEFT JOIN pun_forum_perms AS fp ON ( fp.forum_id = f.id
AND fp.group_id =3 )
WHERE (
fp.read_forum IS NULL
OR fp.read_forum =1
)
AND t.moved_to IS NULL
AND f.id
IN ( 1 )
AND t.posted = p.posted             <-----Changed
ORDER BY t.last_post DESC
LIMIT 15

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: SQL - Select

Yes, that should do it as long as the topic and the post were inserted the same second. They might not have been.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website