Nested subquery search

Question

I have a query that looks up data from a number of tables. One of the values is the equipment IP address. I have a subquery that uses this IP address and searches through a range of IP addresses in a different table and returns a 'vlan_id' if it is within that range. In the equipment table - the ip address field is called 'ip_add'. The IP address is stored as a 16bit unsigned int.

Code:

        SELECT DISTINCT *,
                    equipment.id AS id, 
                    INET_NTOA(ip_add) AS ip_add_c, 
                    INET_NTOA(mcast) AS mcast, 
                    INET_NTOA(lookup) AS lookup, 
                    list_systype.systype_label,
                    list_chgstatus.chgstatus_label  
    FROM `equipment` 
    LEFT JOIN itam_asset on equipment.itamname=itam_asset.ASSETID 
    LEFT JOIN dnslookup on equipment.itamname=dnslookup.itamname 
    LEFT JOIN list_systype on equipment.systype=list_systype.systype_value
    LEFT JOIN list_chgstatus on itam_asset.ASSETLIFECYCLESTATUS=list_chgstatus.chgstatus_value      
    WHERE system_name LIKE :s_name_in 

//get results.....
//Get equipment data
//select and prepare
$database->query($query);
..
..  


foreach($rows as $row){

//inside this loop I have a sub query that finds the vlan_id

$query = "SELECT vlan_id FROM vlan_agg WHERE :ip_add >= ip_sub AND :ip_add <= bcast";
//Get equipment data
//select and prepare
$database->query($query);
//Bind
$database->bind(':ip_add',$ip_add);
$rows1 = $database->resultset(); 
   //execute our query
        $database->execute();

foreach($rows1 as $vl){

//extract row

extract($vl);
#print_r($vl);
}

//display results
...
...
}

The above nested query works well. My question is can I incorporate the sub-query into the main query (and should I do this? I have read that it is very inefficient)

Something like this:

        SELECT DISTINCT *,
                    equipment.id AS id, 
                    INET_NTOA(ip_add) AS ip_add_c, 
                    INET_NTOA(mcast) AS mcast, 
                    INET_NTOA(lookup) AS lookup, 
                    list_systype.systype_label,
                    list_chgstatus.chgstatus_label  
    FROM `equipment` 
    LEFT JOIN itam_asset on equipment.itamname=itam_asset.ASSETID 
    LEFT JOIN dnslookup on equipment.itamname=dnslookup.itamname 
    LEFT JOIN list_systype on equipment.systype=list_systype.systype_value
    LEFT JOIN list_chgstatus on itam_asset.ASSETLIFECYCLESTATUS=list_chgstatus.chgstatus_value

          IN (SELECT vlan_id FROM vlan_agg WHERE ip_add >= ip_sub AND ip_add <= bcast )

WHERE system_name LIKE :s_name_in

I haven't been able to get this to work.

Kind Regards Nigel

If your database supports common table expressions, that may be one way to go. See this for further reference: https://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte — REW, Dec 22 '15 at 00:12
Hi, It is MYSQL. Apparently doesn't support common table expressions. Thanks for input though, I learnt something new :) — Nigel, Dec 22 '15 at 00:31

score 1 · Accepted Answer · answered Dec 22 '15 at 00:57

Just add another LEFT JOIN to the query.

SELECT DISTINCT *,
    equipment.id AS id, 
    INET_NTOA(ip_add) AS ip_add_c, 
    INET_NTOA(mcast) AS mcast, 
    INET_NTOA(lookup) AS lookup, 
    list_systype.systype_label,
    list_chgstatus.chgstatus_label,
    vlan_id
FROM `equipment` 
LEFT JOIN itam_asset on equipment.itamname=itam_asset.ASSETID 
LEFT JOIN dnslookup on equipment.itamname=dnslookup.itamname 
LEFT JOIN list_systype on equipment.systype=list_systype.systype_value
LEFT JOIN list_chgstatus on itam_asset.ASSETLIFECYCLESTATUS=list_chgstatus.chgstatus_value    
LEFT JOIN vlan_agg ON ip_add BETWEEN ip_subAND bcast  
WHERE system_name LIKE :s_name_in

Nested subquery search

1 Answers1