php - 如何加入另一张 table ？

Question

现在我正在使用 UNION 从多个表中获取记录，这很好。但我想包括特定帖子的作者。

$run = mysql_query("            
SELECT article_id AS id, title, smalldesc, hits, coverpic AS picture, timestamp, member_id AS mid, type FROM articles
UNION
SELECT video_id AS id, titel, smalldesc, hits, ytid AS picture, timestamp, member_id AS mid, type FROM videos
UNION
SELECT picture_id AS id, title, smalldesc, hits, coverpic AS picture, timestamp, member_id AS mid, type FROM pictures ORDER BY timestamp DESC LIMIT ".$postnumbers." OFFSET ".$offset."
") or die(mysql_error());

我希望以下选择成为上面代码的一部分:

从成员中选择member_id、图片、fname、lname WHERE member_id='mid'

我想从每个帖子中获取成员，这样我就可以打印出帖子的发布者。

Answer 1

按照您的要求，您必须将 Members 表连接到 UNION 的三部分查询:

SELECT 
    a.article_id AS id, 
    a.title, 
    a.smalldesc, 
    a.hits, 
    a.coverpic AS picture, 
    a.timestamp, 
    a.member_id AS mid, 
    a.type,
    m.picture, 
    m.fname, 
    m.lname 
FROM 
    articles a
INNER JOIN 
    members m
ON
    a.member_id = m.member_id
UNION
SELECT 
    v.video_id, 
    v.titel, 
    v.smalldesc, 
    v.hits, 
    v.ytid AS picture, 
    v.timestamp, 
    v.member_id AS mid, 
    v.type 
    m2.picture, 
    m2.fname, 
    m2.lname 
FROM 
    videos v
INNER JOIN
    members m2
ON
    v.member_id = m2.member_id
UNION
    ...

获取此信息。