using results from one mysql for a second query

Question

I have two tables:

• posts - holds post information
• listen - holds information on what other users you are listening to. (what users posts you want to view)

The structure of listen is:

• id(uniqueid)
• userid(the users unique id)
• listenid(id of a user they are listening too)

How would I gather all the entries from listen that mach the active users userid and then use those to find all the posts that match any of the found listenid values so as to create a query of the combined users posts I want to view?

Answer 1

a simple join wont work?

select 
 posts.* 
from 
 posts
inner join 
 listen
on 
 listen.listenID = posts.userID
where 
 listen.userID = ACTIVEUSER

Answer 2

I think you're talking about something like this:

select postid from posts 
where userid in
(
    select listenid from listen
    where userid = CURRENT-USER
)

This is assuming the table posts has a userid field.

Answer 3

You can do this with a simple natural join, or a direct join as given in other answers.

select 
  *
from 
  posts, listen 
where 
  listen.userid == $active_user and 
  posts.userid = listen.userid

You probably want to be more selective about the columns you are bringing in.

Answer 4

SELECT  posts.*
FROM    listen
JOIN    posts
ON      posts.userid = listen.listenid
WHERE   listen.userid = @current_user