获取用户帖子与
我想从该posts
表中的当前用户的好友帖子的用户的朋友的帖子,我已经试过是这样的代码:获取用户帖子与
SELECT
`p`.id,
`p`.thetext,
`p`.time,
`p`.location
FROM
`friends` as f,
`users` as u,
`posts` as p
WHERE
`f`.frid = `u`.id
AND
`p`.publisher = `u`.username
AND
`f`.username = '$userusername'
ORDER BY
`p`.orderdate DESC
此代码获取用户的朋友的帖子,但我想显示的用户的帖子和用户的朋友的帖子(与排序)。
请问该做什么?
SELECT
`p`.id,
`p`.thetext,
`p`.time,
`p`.location
FROM `posts` as p
INNER JOIN `users` as u ON `p`.publisher = `u`.username
LEFT JOIN `friends` as f ON `f`.frid = `u`.id
WHERE
`f`.username = '$userusername'
ORDER BY
`p`.orderdate DESC
请尝试上面的代码。 希望这会有所帮助。
这个查询并没有给我带来什么 –
你确定每个发表帖子的用户都有'posts'和'user'表中的条目,''p'.publisher ='u'.username'会一直返回true。 –
不,也许用户还没有发布帖子呢!这意味着用户可能没有文章 –
SELECT
`p`.id,
`p`.thetext,
`p`.time,
`p`.location
FROM
`friends` as f,
`users` as u,
`posts` as p
WHERE
`f`.frid = `u`.id
AND
(`p`.publisher = `u`.username or `p`.publisher = '$userusername')
AND
`f`.username = '$userusername'
ORDER BY
`p`.orderdate DESC
它的工作,但有重复的列! –
不同的帖子id然后再试一次'SELECT distinct('p'.id), 'p'.thetext, 'p'.time, 'p'.location FROM' –
好吧......看起来您需要了解数据库模式和SQL。当事情相对简单且易于理解时,您应该现在就做到这一点。请不要在SO上提出这个问题。 –
谢谢你的建议,但你能回答我的问题吗? –
你能提一下你在查询中使用的所有表吗? –