Tricky Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Base
Forum Newbie
Posts: 13
Joined: Thu May 27, 2004 6:12 am

Tricky Query

Post 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.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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.
Base
Forum Newbie
Posts: 13
Joined: Thu May 27, 2004 6:12 am

Post 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.
kevin7
Forum Commoner
Posts: 96
Joined: Fri May 21, 2004 6:54 am

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Post Reply