SQL query (Join without duplicates)

Question

I have tables users and topics. Every user can have from 0 to several topics (one-to-many relationship).

How I can get only those users which have at least one topic?

I need all columns from users (without columns from topics) and without duplicates in table users. In last column I need number of topics.

UPDATED:

Should be like this:

SELECT user.*, count(topic.id) 
FROM ad
LEFT JOIN topic ON user.id = topic.ad
GROUP BY user.id
HAVING count(topic.id) > 0;

but it takes 0 result. But it should not be 0.

Answer 1

Firstly you need to have your two tables, because you have left limited information about your table structure I will use an example to explain how this works, you should then be able to easily apply this to your own tables.

Firstly you need to have two tables (which you do)

Table "user"

id | name
1  | Joe Bloggs
2  | Eddy Ready

Table "topic"

topicid | userid | topic
      1 |      1 | Breakfast
      2 |      1 | Lunch
      3 |      1 | Dinner

Now asking for a count against each user is done using the follwing;

SELECT user.name, count(topic.topicid) 
FROM user
INNER JOIN topic ON user.id = topic.userid
GROUP BY user.name

If you use a left join, this will include records from the "user" table which does not have any rows in the "topic" table, however if you use an INNER JOIN this will ONLY include users who have a matching value in both tables.

I.e. because the user id "2" (which we use to join) is not listed in the topic table you will not get any results for this user.

Hope that helps!