NodeJS Mysql Query – How to Join 2 Selects in a Select?

Issue

I’m using NodeJS for a a complex query in Mysql. Firstly I need to grab tagid1, tagid2 and tagid3 from the entitytag table. Then I need to use those fetched tagids to get the tagname to each tagid in the tags table. Here is a query that produces an error which tells me that "tagid1 is not defined".

 row_c = await db.query( `SELECT 
        ap.audioname, 
        ap.userid, 
        ap.audioid, 
        ap.title, 
        ap.likes, 
        u.gavatar,  
        u.name, 
        u.email,
        (SELECT tagid1 FROM entitytag et LEFT JOIN tags t on t.tagid = et.tagid1 WHERE et.audioid = ap.audioid)tagid1,  
        (SELECT tagid2 FROM entitytag et LEFT JOIN tags t on t.tagid = et.tagid2 WHERE et.audioid = ap.audioid)tagid2, 
        (SELECT tagid3 FROM entitytag et LEFT JOIN tags t on t.tagid = et.tagid3 WHERE et.audioid = ap.audioid)tagid3,
        (SELECT tagname FROM tags WHERE tagid = ?),
        (SELECT tagname FROM tags WHERE tagid = ?),
        (SELECT tagname FROM tags WHERE tagid = ?)
        FROM audioposts ap LEFT JOIN accounts u ON u.id = ap.userid WHERE ap.audioid = ?`, [tagid1, tagid2, tagid3, audioid ]);

I don’t actually need the return of tagid1, tagid2, tagid3. I’m simply using them to fetch the tagnames from the tags table.

What am I doing wrong? How do I define "tagid1"?

Solution

I am suggesting a rewrite of your query after a few observations. I noticed a few similarities throughout your code and also observed that you stated that you are trying to retrieve tagid1-3 first.

As such, I have rewritten the query to utilize LEFT JOINs as this will include the details if available and aliases for simplification.

Now the query retrieves the entitytag record based on the et.audioid = ap.audioid as shown in your original query but does not need the tagId as a query paramter i.e. here [tagid1, tagid2, tagid3, audioid ] since the objective, garnered from your select/projection is to retrieve the tagId and tagname of tagid1-3.

Now that we would match the entitytag record if it exists, we may continue by retrieving the related tag information from the tag table. I have done this using 3 additional LEFT JOINs to the same table but with differentiating aliases eg LEFT JOIN tags t{num} ON et.tagid1 = t{num}.tagid where num represented the 1-3 for each tag. If these details are available they are retrieved as non-null values in the projection using

    t1.tagId as tagId1,
    t1.tagname as tagname1,
    t2.tagId as tagId2,
    t2.tagname as tagname2,
    t3.tagId as tagId3,
    t3.tagname as tagname3

Finally, since the query retrieves the desired tagId details these are no longer passed as parameters. This results in just one parameter for the audioid i.e. WHERE ap.audioid = ?, [audioid ])`.

I have included the suggested query below. Please let me know if this works for you.

row_c = await db.query( `
SELECT 
    ap.audioname, 
    ap.userid, 
    ap.audioid, 
    ap.title, 
    ap.likes, 
    u.gavatar,  
    u.name, 
    u.email,
    t1.tagId as tagId1,
    t1.tagname as tagname1,
    t2.tagId as tagId2,
    t2.tagname as tagname2,
    t3.tagId as tagId3,
    t3.tagname as tagname3
FROM 
    audioposts ap 
LEFT JOIN 
    accounts u ON u.id = ap.userid 
LEFT JOIN
    entitytag et ON et.audioid = ap.audioid
LEFT JOIN
    tags t1 ON et.tagid1 = t1.tagid
LEFT JOIN
    tags t2 ON et.tagid2 = t2.tagid
LEFT JOIN
    tags t3 ON et.tagid3 = t3.tagid

WHERE ap.audioid = ?`, [audioid ]);

Answered By – ggordon

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0