php - 嵌套子查询搜索

Question

我有一个从多个表中查找数据的查询。其中一个值是设备 IP 地址。我有一个子查询，它使用此 IP 地址并搜索不同表中的 IP 地址范围，如果它在该范围内则返回“vlan_id”。在设备表中——IP 地址字段称为“ip_add”。 IP 地址存储为 16 位无符号整数。

代码:

        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
...
...
}

上面的嵌套查询运行良好。我的问题是我可以将子查询合并到主查询中吗(我应该这样做吗？我读到它非常低效)

像这样:

        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

我还没能让它工作。

亲切的问候奈杰尔

Answer 1

只需在查询中添加另一个 LEFT JOIN。

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