PHP/MySQL - Join two Joins?

3

Would it be possible to join the following two queries so that the tags for each audioid are included in the returned array?

"SELECT audioposts.audioname, audioposts.userid, audioposts.audioid, users.gavatar, //users.name, users.email, audioposts.title, audioposts.likes, audioposts.userid FROM audioposts LEFT JOIN //users ON users.id = audioposts.userid"

"SELECT tagname FROM entitytag LEFT JOIN tags ON entitytag.tagid = tags.tagid WHERE entitytag.audioid = '$audioid'"

 while($row=$result->fetch_array())
                {
                array_push($spacecrafts, array(
                "name"=>$row['name'],
                "audioname"=>$row['audioname'],
                "email"=>$row['email'],
                "title"=>$row['title'],
                "likes"=>$row['likes'],
                "userid"=>$row['userid'],
                "gavatar"=>$row['gavatar'],
                "audioid"=>$row['audioid']                      

));
            }

answered question

Help us help you - share some sample data and the result you'd like to get for it

1 Answer

6

It looks like you just want more left joins:

SELECT 
    ap.audioname, 
    ap.userid, 
    ap.audioid, 
    u.gavatar, 
    u.name, 
    u.email, 
    ap.title, 
    ap.likes, 
    ap.userid,
    t.tagname
FROM audioposts ap 
LEFT JOIN users u ON u.id = ap.userid
LEFT JOIN entitytag et on et.audioid = dp.audioid
LEFT JOIN tags t on t.tagid = et.tagid

I suspect that this would multiply the rows, since an audiopost probably might have several tags. You might be looking to gather all tag names together, for example using string aggregation. A correlated subquery would make sense for that:

SELECT 
    ap.audioname, 
    ap.userid, 
    ap.audioid, 
    u.gavatar, 
    u.name, 
    u.email, 
    ap.title, 
    ap.likes, 
    ap.userid,
    (
        SELECT GROUP_CONCAT(t.tagname)
        FROM entitytag et
        LEFT JOIN tags t on t.tagid = et.tagid
        WHERE et.audioid = dp.audioid
    ) tagnames
FROM audioposts ap 
LEFT JOIN users u ON u.id = ap.userid

posted this

Have an answer?

JD

Please login first before posting an answer.