Combine Two Queries into One?

I have a MySQL query that selects all audioids from a certain user in a subscribe table.

I then have another query which takes this list of audioids and matches them against a field called opids in a table called audioposts. It then selects the titles from that audioposts table and joins the users from a users table at the userid.

Is there a way I can turn these two queries into one query?

query1 = "SELECT audioid FROM subscribe WHERE userid = $userid";                            
                    
query2 = "SELECT ap.audioid, ap.title, us.name FROM audioposts ap                   
        INNER JOIN audioposts a2 ON a2.audioid = ap.opid
        INNER JOIN users us ON us.id = a2.userid 
        WHERE ap.opid = $newaudio";

Here is the current two query code which I’d like to replace with one query. I’ve not yet translated this into prepared statements as it’s easier for me to visualize it the old-fashioned way. Plus I’ll be converting this into NodejS eventually anyway;

    $selectaudioid = "SELECT audioid FROM subscribe WHERE userid = $userid";
    $audioResult=$dblink->query($selectaudioid);
    
        if ($audioResult->num_rows>0)   {       
            while ($row = $audioResult->fetch_assoc())  {
                $newaudio = $row[audioid];  
                $getallaudio = "                
                SELECT ap.audioid, ap.title, us.name FROM audioposts ap                     
                INNER JOIN audioposts a2 ON a2.audioid = ap.opid
                INNER JOIN users us ON us.id = a2.userid 
                WHERE ap.opid = $newaudio AND ap.opid <> '0'";
                
                $getallresult = $dblink->query($getallaudio);           
                
                if ($getallresult->num_rows>0)  {                       
                while ($row = $getallresult->fetch_assoc())  {
                    $dbdata[]=$row;                         
        }}}}

Answer

Just add another join with subscribe.

SELECT ap.audioid, ap.title, us.name 
FROM audioposts ap                   
INNER JOIN audioposts a2 ON a2.audioid = ap.opid
INNER JOIN subscribe s ON s.audioid = ap.opid
INNER JOIN users us ON us.id = a2.userid 
WHERE s.userid = $userid AND ap.opid <> '0'

Advertisement

Answer