SQL join query

Ziycon

SELECT cf_topics.id,cf_topics.board_id,cf_topics.topic_name,cf_topics.orig_poster,cf_topics.replies,cf_topics.views,cf_posts.topic_id,cf_posts.date,cf_posts.time,cf_posts.poster 
FROM cf_topics,cf_posts 
WHERE (cf_topics.board_id = '.$board_id.') AND (cf_topics.id = cf_posts.topic_id) 
ORDER BY cf_posts.date DESC, cf_posts.time DESC

There is two results for the query but both results are returned twice!??

There is two tables one has the topic information and the other has all the posts for the topic, if a topic has 3 posts in it the topic will show up 3 times in the topic listings when each topic should only show up once!

smcelhinney

Use GROUP BY ...

SELECT cf_topics.id,cf_topics.board_id,cf_topics.topic_name,cf_topics.orig_poster,cf_topics.replies,cf_topics.views,cf_posts.topic_id,cf_posts.date,cf_posts.time,cf_posts.poster 
FROM cf_topics,cf_posts 
WHERE (cf_topics.board_id = '.$board_id.') AND (cf_topics.id = cf_posts.topic_id) 
GROUP BY <unique_field>
ORDER BY cf_posts.date DESC, cf_posts.time DESC

Also, strictly speaking, this is not a JOIN. AFAIK, multiple WHERE clauses are less efficient than JOINs. INNER JOINS show unique entries from the first selected table, with the appropriate fields from the joined table. JOINs implicitly include fields from the joined table for selection.

Could rewrite the above like this:

SELECT <fields>
FROM cf_posts
INNER JOIN cf_topics ON cf_posts.topic_id = cf_topics.id
WHERE cf_topics.board_id = '.$board_id.'
ORDER BY cf_posts.date DESC, cf_posts.time DESC

HTH
Stephen

Ziycon

The fields i enter in the rewritten code, do i put the table name before them? its giving me errors if i do it like this:

SELECT cf_topics.id,cf_topics.board_id,cf_topics.topic_name,cf_topics.orig_poster,cf_topics.replies,cf_topics.views,cf_posts.topic_id,cf_posts.date,cf_posts.time,cf_posts.poster 
INNER JOIN cf_topics ON cf_posts.topic_id = cf_topics.id 
WHERE cf_topics.board_id = '.$board_id.' 
ORDER BY cf_posts.date DESC, cf_posts.time DESC

Ziycon

Got the joins working fine, now i have added in the group by but its only showing one post on a topic even if its got more!

smcelhinney

Missing the FROM criteria in your first one..

Dont put in the GROUP BY clause with INNER JOINs, the statement will return unique records using your WHERE criteria, and "join" the fields from other tables using the identifier you have specified.

What are you trying to return? All posts under a specific topic? Or topic information for a specific post?

What does this statement return?

SELECT cf_topics.id, cf_topics.board_id, cf_topics.topic_name, cf_topics.orig_poster, cf_topics.replies, cf_topics.views,cf_posts.topic_id, cf_posts.date,cf_posts.time, cf_posts.poster
FROM cf_posts
INNER JOIN cf_topics ON cf_posts.topic_id = cf_topics.id
WHERE cf_topics.board_id = '.$board_id.'
ORDER BY cf_posts.date DESC, cf_posts.time DESC

Ziycon

SELECT cf_topics.id, cf_topics.board_id, cf_topics.topic_name, cf_topics.orig_poster, cf_topics.replies, cf_topics.views,cf_posts.topic_id, cf_posts.date,cf_posts.time, cf_posts.poster
FROM cf_posts
INNER JOIN cf_topics ON cf_posts.topic_id = cf_topics.id
WHERE cf_topics.board_id = '.$board_id.'
ORDER BY cf_posts.date DESC, cf_posts.time DESC

This returns all the topics/threads. Its retrieving info from two tables, one had topic information and the other table has all the info for all posts.

Beano

smcelhinney wrote:

Use GROUP BY ...
...Also, strictly speaking, this is not a JOIN. AFAIK, multiple WHERE clauses are less efficient than JOINs. INNER JOINS show unique entries from the first selected table, with the appropriate fields from the joined table. JOINs implicitly include fields from the joined table for selection.
...

HTH
Stephen

Strictly speaking it was a JOIN. Depending on the flavour of SQL you use it might be the only way to do a join. Efficiency wise I have never seen it make any difference whether you use ANSI or non-ANSI joins and everything I've read confirms that.

Beano

Ziycon wrote:

SELECT cf_topics.id, cf_topics.board_id, cf_topics.topic_name, cf_topics.orig_poster, cf_topics.replies, cf_topics.views,cf_posts.topic_id, cf_posts.date,cf_posts.time, cf_posts.poster
FROM cf_posts
INNER JOIN cf_topics ON cf_posts.topic_id = cf_topics.id
WHERE cf_topics.board_id = '.$board_id.'
ORDER BY cf_posts.date DESC, cf_posts.time DESC

This returns all the topics/threads. Its retrieving info from two tables, one had topic information and the other table has all the info for all posts.

So what is it you actually want this query to return?

Ziycon

i want it to return one copy of each topic to be listed cause at the moment when the topics are displaied if there is say 3 replies to a topic it will list that topic 3 times in the topic listings instead of just once.

Beano

Ziycon wrote:

i want it to return one copy of each topic to be listed cause at the moment when the topics are displaied if there is say 3 replies to a topic it will list that topic 3 times in the topic listings instead of just once.

In that case why do you even need to include the Posts table in your query? Just select the columns you want from the Topics table.

Ziycon

Beano wrote:

In that case why do you even need to include the Posts table in your query? Just select the columns you want from the Topics table.

The way the tables are laid out is to stop replication of data, below is a brief decription of the tables:

cf_topics

id
board_id
topic_name
orig_poster
replies
views

cf_posts

id
topic_id
body
poster
date
time
ip

Im retrieving everything from cf_topics and retrieving poster, date and time from cf_posts to display the last poster on the topic!

Ziycon

Got it sorted, thanks for all the help!