SQL syntax with more than one row.

Question

Hello.

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!

Answer 1

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?

Answer 2

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.

Answer 3

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.