Page 1 of 1
Using in() with a join
Posted: Sat Aug 01, 2009 11:20 am
by jackpf
Hello everyone,
For a search feature for my forum, I'm trying to implement the ability to search only threads you have posted in.
So basically, I need to select threads, with at least one post that the user has made.
In my current query, P.`Author` is from a join consisting of all posts for the thread. I would have thought
Code: Select all
HAVING '$username' IN (P.`Author`)
would work, but I guess it doesn't, since I am GROUPing by id.
I'm not quite sure how to implement this...any help would be greatly appreciated.
Here's (a simplified version of) my full query:
Code: Select all
SELECT F.*, COUNT(P.`ID`) AS `PostCount`, MAX(P.`ID`) AS `LastPost`
FROM `Forum` F
INNER JOIN `Forum` P ON F.`ID` = P.`Thread` AND P.`Type`='post'
WHERE F.`Type`='thread'
GROUP BY F.`Author`
HAVING '$username' IN(P.`Author`) /*<- this is where I'm trying to select only threads with a post in by the user*/
ORDER BY F.`ID` ASC
Thanks for any replies,
Jack.
Re: Using in() with a join
Posted: Sat Aug 01, 2009 11:53 am
by Eran
To do what you described you would need a subquery. have the subquery select the IDs of the posts (grouped) and then join that to the Forum table again to select the rest of the details.
Why are you inner joining the forum table? is that a flat table for both threads and individual posts? is there a user table as well?
Re: Using in() with a join
Posted: Sun Aug 02, 2009 12:13 pm
by jackpf
Hmm....right. Sorry, how would I do that? An example would be great.
And yeah, at the moment I just have one table for everything - boards, threads and posts...so I need to join the same table on itself.
And yeah, there is a users table. But the username is stored in the field `Author`...so there shouldn't be any need for the users table in this query.
Thanks for the reply

All the best,
Jack.
Re: Using in() with a join
Posted: Sun Aug 02, 2009 3:29 pm
by Eran
Since you didn't post your table structure it's a bit hard, but it would be something like this:
Code: Select all
SELECT F.*, COUNT(P.`ID`) AS `PostCount`, MAX(P.`ID`) AS `LastPost`
FROM `Forum` F
INNER JOIN `Forum` P ON F.`ID` = P.`Thread`
INNER JOIN (
SELECT id FROM `Forum`
WHERE `Type`='post'
AND Author='$username'
) AS pid ON pid.id=P.id
WHERE F.`Type`='thread'
ORDER BY F.`ID` ASC
The derived subquery returns the IDs of the posts you want. You will have to add filtering conditions as per your needs (right now it selects everything the author has written).
On a side note - using a flat denormalized table for this makes this query harder than it should be. Personally I would use separate table for threads and posts (with a one-to-many relationship)
Re: Using in() with a join
Posted: Sun Aug 02, 2009 4:22 pm
by jackpf
Aha! I have it!!
I based it on your example, but didn't have the subquery.
Basically, I just inner joined the forum table with a post in that thread. And since it's an inner join, if a post isn't returned, then the thread won't return! Genius
That works perfectly.
Thanks a lot for the help pytrin - I really appreciate it. And yes, I should know better...sorry for not posting my table structure.
Thanks again,
All the best,
Jack.
Re: Using in() with a join
Posted: Sun Aug 02, 2009 7:15 pm
by Eran
Yeah, I noticed in the end that no grouping was involved in the subquery, so it can be translated to a regular join. Maybe you should post the solution you have for people with a similar problem in the future
Re: Using in() with a join
Posted: Mon Aug 03, 2009 7:20 am
by jackpf
I was going to....but my query is pretty complicated.
Here is the join that I used, with a bit of an explanation:
Code: Select all
INNER JOIN `Forum` /*joining the same table with an inner join, so that if the user hasn't posted in this thread, the thread won't be returned*/ P2
ON P2.`Forum`=F.`Forum` /*select posts that are in this forum*/
AND P2.`Thread`=P.`Thread` /*and that are in this thread*/
AND P2.`Type` IN('thread', 'post') /*where the type is a post (within the thread) or the type is thread, and the post is the thread itself*/
AND P2.`Author`='$username' /*and the author is whoever is searching*/
Thanks again for your help pytrin
Regards,
Jack.