php - 嵌套子查询搜索

我有一个查询，可以从多个表中查找数据。
其中一个值是设备IP地址。
我有一个子查询，它使用这个IP地址，并在另一个表中搜索一个IP地址范围，如果在这个范围内，则返回一个“vlan\U 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

我还没能把这个用上。
亲切的问候
奈杰尔

最佳答案

只需在查询中添加另一个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