使用一个mysql的结果进行第二次查询

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)

listen - 保存您正在收听的其他用户的信息。 (您要查看的用户帖子)

The structure of listen is:

listen的结构是:

id(uniqueid)
userid(the users unique id)

userid(用户唯一ID)

listenid(id of a user they are listening too)

listenid(他们正在听的用户的id)

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?

我如何收集来自监听活动用户userid的所有条目,然后使用这些条目查找匹配任何找到的listenid值的所有帖子,以便创建我想要查看的组合用户帖子的查询?

4 个解决方案

#1

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

#2

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.

您可能希望对所引入的列更具选择性。

#3

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.

这假设表格帖子有一个用户标识字段。

#4

a simple join wont work?

一个简单的加入不会工作?

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

#1

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

#2