Using in() with a join

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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Using in() with a join

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Using in() with a join

Post 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?
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Using in() with a join

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Using in() with a join

Post 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)
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Using in() with a join

Post 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 :D

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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Using in() with a join

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Using in() with a join

Post 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 :bow:

Regards,
Jack.
Post Reply