SQL syntax with more than one row
I've tried in 1 hour now, trying to find out how I could extract something from the database, in a INNER JOIN with a simple SQL syntax.
It works, nearly and my question is how开发者_高级运维 can i select more than only one row from the table.
My SQL syntax:
SELECT topics.topic_id, topics.topic_subject, COUNT(posts.post_topic) AS comments
FROM topics
INNER JOIN posts ON topics.topic_id = posts.post_topic = topics.topic_id
LIMIT 10
And of course I don't wanna use the WHERE statement and it doesn't works without WHERE either. Btw. I think it's wrong after ON, but I'm not quite sure there.
Thanks!
Try:
SELECT topics.topic_id, topics.topic_subject, COUNT(posts.post_topic) AS comments
FROM topics
JOIN posts ON topics.topic_id = posts.post_topic
GROUP BY topics.topic_id, topics.topic_subject
LIMIT 10
COUNT is a summary function, but you're not telling the DB how to summarize (i.e. roll up) the data. The GROUP BY clause states that the data should be rolled up based on topic_id and topic_subject.
If you only select rows where topic_id IS NULL, what do you expect to get? (I'm assuming it's the primary key.) I'd expect to get nothing, unless there were some orphaned posts in the posts table.
Do you really need the extra topics.topic_id
?
SELECT topics.topic_id, topics.topic_subject, COUNT(posts.post_topic) AS comments FROM topics INNER JOIN posts ON topics.topic_id = posts.post_topic WHERE topic_id = 'NULL' LIMIT 10
Also, are you sure there is more than one null row in the table?
The notation:
ON topics.topic_id = posts.post_topic = topics.topic_id
might be interpreted as:
ON topics.topic_id = (posts.post_topic = topics.topic_id)
which could be regarded as "compare topic_id with post_topic and return TRUE or FALSE; then compare the topic_id with TRUE or FALSE and only compare those that match", with (quite possibly) TRUE being converted to 1 and FALSE to 0.
Or the grouping might be:
ON (topics.topic_id = posts.post_topic) = topics.topic_id
with the same net result.
Strong recommendation: remove the superfluous equality condition!
ON topics.topic_id = posts.post_topic
It confuses the readers, even if it doesn't confuse the DBMS.
精彩评论