Page 1 of 1

Tricky Query

Posted: Thu May 27, 2004 6:12 am
by Base
OK, I have two tables, one for posts, the other for replies.

I would like for the query to find distinct posts and replies where tablename.username = '$username'.

This is my current code:

Code: Select all

SELECT DISTINCT
	canterbury_posts_out.post_id, canterbury_posts_out.username,
	canterbury_posts_out.category, canterbury_posts_out.subject,
	canterbury_posts_out.reply_time
	FROM canterbury_posts_out, canterbury_replies_out
	WHERE canterbury_posts_out.post_id = canterbury_replies_out.post_id
	AND canterbury_posts_out.username =  '$username'
	OR canterbury_replies_out.username =  '$username'
	AND canterbury_posts_out.post_id = canterbury_replies_out.post_id
	ORDER  BY canterbury_posts_out.reply_time DESC
The only rows that return are posts that the $username has posted, and was replied to by someone. I know this is because of this row:

Code: Select all

AND canterbury_posts_out.post_id = canterbury_replies_out.post_id
But I need to know how to code it so it will return posts without replies. I'm having a lot of trouble with this.

Posted: Thu May 27, 2004 6:36 am
by magicrobotmonkey
Yea, that line (in their twice?) will limit the results to only posts that have a reply becuase otherwise there will be no canterbury_replies_out.post_id to be equal to. In your Select part, however, you never select anything from the replies table. I'm not sure what's up with that. To select everything with the username, canterbury_posts_out.username = '$username' should work fine.

Posted: Thu May 27, 2004 7:01 am
by Base
Yes, but I wanted it to display the posts AND the replies.

I was able to do it with this:

Code: Select all

(SELECT DISTINCT * FROM canterbury_posts_out
	WHERE username = '$username')
	
	UNION

	(SELECT DISTINCT * FROM canterbury_replies_out
	WHERE username = '$username')
	
	ORDER BY post_id DESC
However, I get repeats because of the distinct reply-post times.

Posted: Thu May 27, 2004 10:04 am
by kevin7
hmm... actually, can u tell me the output u want?
u code was kinda confusing... u can modify into this...

Code: Select all

SELECT DISTINCT 

a.post_id, a.username, a.category, a.subject, a.reply_time 

FROM canterbury_posts_out a, canterbury_replies_out 

WHERE a.post_id = canterbury_replies_out.post_id 

AND a.username =  '$username' 

OR a.username =  '$username' 

ORDER  BY a.reply_time DESC

Posted: Thu May 27, 2004 12:11 pm
by Weirdan
Actually to get posts without replies you need left join instead of inner join you use. Consult the mysql manual to learn the syntax.