php - SQL 在自连接结构中全部获取

Question

我当前的数据库看起来像

CREATE TABLE sites(
siteId INT(11) AUTO_INCREMENT,
siteType VARCHAR(255),
siteName VARCHAR(255),
siteDomain VARCHAR(255),
PRIMARY KEY(siteId)
);
CREATE TABLE siteSites(
parentId INT(11),
childId INT(11)
)

我正在尝试连接所有表并获取所有数据。喜欢:

<?php
$q=mysql_query("SELECT * FROM sites s1, siteSites, sites s2 WHERE s1.siteId=parentId AND s2.siteId=childId");
$row=mysql_fetch_array($q);
?>

而且我想从 $row 变量中获取 's1' 和 's2' 的信息。这可能吗？如果可能，我该怎么做？

谢谢

Answer 1

SELECT s1.siteId as ParentSiteId, s1.siteType as ParentType, s1.siteName as ParentName, s1.siteDomain as ParentDomain,
       s2.siteId as ChildSiteId,  s2.siteType as ChildType,  s2.siteName as ChildName,  s2.siteDomain as ChildDomain
    FROM sites s1
        INNER JOIN siteSites ss
            ON s1.siteId = ss.parentId
        INNER JOIN sites s2
            ON ss.childId = s2.siteId