Join two Joins?

Question

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'

Help us help you - share some sample data and the result you'd like to get for it — Mureinik, Aug 01 '20 at 14:46
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 01 '20 at 20:38
Joining these queries might be possible, but it's very unclear if this makes sense. You need to share more details — Nico Haase, Aug 02 '20 at 14:29

GMB · Accepted Answer · 2020-08-01T15:09:44.413

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 = ap.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 = ap.audioid
    ) tagnames
FROM audioposts ap 
LEFT JOIN users u ON u.id = ap.userid

You are correct in that each audioid has three tags. So i tried your 2nd option but I'm guessing I've made an error. My PHP logs say; "Trying to get property 'num_rows' of non-object " . Will double check my code. — Meggy, Aug 01 '20 at 15:08
@Meggy: there was a typo in the subquery, `ad.audioid` had to be `ap.audioid`. You should learn to trap and report SQL errors in your PHP code - it makes debugging SQL errors *much* easier. — GMB, Aug 01 '20 at 15:12

Join two Joins?

1 Answers1