需要简单加入方面的帮助

发布于 2024-09-09 02:02:47 字数 287 浏览 6 评论 0原文

哎呀，

说对了。

SELECT *,t.id AS threadid FROM threads t 
LEFT JOIN players p on p.id = t.last_poster 
WHERE t.boardid = $boardid

我在线程中有两个名为 posterid 和 lastposterid 的字段。这是话题发起者/最后发帖者的 ID。我想做的是从玩家表中获取他们的名字。

但如何呢？

原文

Right to the problem.

SELECT *,t.id AS threadid FROM threads t 
LEFT JOIN players p on p.id = t.last_poster 
WHERE t.boardid = $boardid

I have two fields in threads called posterid and lastposterid. Which are the IDs of the thread starter / last poster. What I want to do is to get their names from players table.

But how?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

羁拥 2024-09-16 02:02:47

您只需加入玩家表两次，就像这样。

SELECT
    threads.*,
    starterPlayer.*,
    lastPosterPlayer.*
FROM
    threads
LEFT OUTER JOIN
    players starterPlayer
ON
    starterPlayer.id = threads.posterid
LEFT OUTER JOIN
    players lastPosterPlayer
ON
    lastPosterPlayer.id = threads.lastposterid

You just need to join to your players table twice, like this.

SELECT
    threads.*,
    starterPlayer.*,
    lastPosterPlayer.*
FROM
    threads
LEFT OUTER JOIN
    players starterPlayer
ON
    starterPlayer.id = threads.posterid
LEFT OUTER JOIN
    players lastPosterPlayer
ON
    lastPosterPlayer.id = threads.lastposterid

回复收藏 0 原文

葵雨 2024-09-16 02:02:47

您可以两次联接同一个表并为该表指定不同的别名。

这假设总是会有第一个和最后一个海报，如果是这种情况，那么您需要一个 INNER JOIN 而不是 LEFT JOIN，您将需要更改select 语句来获取相关字段。

SELECT t.id AS threadid, playerFirst.name AS FirstPoster, playerLast.name as LastPoster 
FROM threads t
INNER JOIN
   players playerFirst ON playerFirst.id = t.posterid
INNER JOIN 
   players playerLast ON playerLast.id = t.lastposterid

You can join to the same table twice and give the table a different alias.

This presumes that there always will be a first and last poster, if this is the case then you want an INNER JOIN rather than a LEFT JOIN, you will need to change the select statement to get the relevant fields.

SELECT t.id AS threadid, playerFirst.name AS FirstPoster, playerLast.name as LastPoster 
FROM threads t
INNER JOIN
   players playerFirst ON playerFirst.id = t.posterid
INNER JOIN 
   players playerLast ON playerLast.id = t.lastposterid

回复收藏 0 原文

风为裳 2024-09-16 02:02:47

怎么样...

SELECT *,
    (SELECT name
         FROM players
         WHERE players.id = threads.posterid) AS poster,
    (SELECT name
         FROM players
         WHERE players.id = threads.lastposterid) AS last_poster
    FROM threads;

How about...

SELECT *,
    (SELECT name
         FROM players
         WHERE players.id = threads.posterid) AS poster,
    (SELECT name
         FROM players
         WHERE players.id = threads.lastposterid) AS last_poster
    FROM threads;

回复收藏 0 原文

~没有更多了~