CODEWITHSUNDEEP
phpmysql
Niels Hermann
Niels Hermann

Reputation: 538

How to join another table?

Right now I'm using UNION to get records from multiple tables, which is fine. But I want to include the author for the specific post.

$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());

I want the following select to be a part of the code above:

SELECT member_id, picture, fname, lname FROM members WHERE member_id='mid'

I want to get the member from each post, so I can print out who made the post.

Upvotes: 0

Views: 40

Answers (1)

VMai
VMai

Reputation: 10336

You've got, as you asked, to join the members table to the three part queries of the 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
    ...

to get this information.

Upvotes: 2

Related Questions