php - SQL SELECT 语句从具有外键和关联关系的多个表中提取数据

Question

我有以下表格:

1. 大学(iduni, uniname)
2. 校区(idcampus、iduni、campusname、campusaddress)
3. 项目(idproject、idcampus、projectname)
4. IndustryCode( idindustrycode, codenumber, description )
5. 特化(idspec，specname)

下面是上面列出的一些表的关联表

1. CampusSpecialization(idcampus，idspec)
2. ProjectSpecialization(idproject，idSpec)
3. 项目行业代码( idproject,idindustrycode )

我想通过 PHP 生成具有以下结构的 XML 文件，但无法弄清楚 SELECT 语句从数据库中提取数据。数据是每个项目的校园、大学、项目、行业代码和专业，以便我稍后可以根据这些相互关联的数据在我的小型应用程序中过滤结果。

    <items>
        <item>
            <campus campusname="$CAMPUSNAME" uniname="$UNINAME" campusaddress="$CAMPUSADDRESS">
                <projects>
                    <project>
                        <name>$PROJECTNAME</name>
                        <specs>
                            <spec>$SPECNAME1</spec>
                            <spec>$SPECNAME2</spec>
                            ...
                        </specs>
                        <industries>
                            <industry>$CODENUMBER1</industry>
                            <industry>$CODENUMBER2</industry>
                            ...
                        </industries>
                    </project>
...
                </projects>
            </campus>
        </item>
        <item>
            <campus ... >
                <projects>
                    <project ... >
                        ...
                    </project>
            </campus>
    </items>

到目前为止，这是我的 SQL 语句:

SELECT
  campusname, 
  specname,
  projectname
FROM
  Specialization,Projects
  JOIN CampusSpecialization ON CampusSpecialization.idspec = Specialization.idspec
  JOIN Campuses ON CampusSpecialization.idcampus = Campuses.idcampus
  JOIN ProjectSpecialization ON ProjectSpecialization.idspec = Specialization.idspec

编辑:

项目可能有一个或多个代码和行业。

Answer 1

试试这个:

SELECT c.campusname,u.uniname,c.campusaddress, s.specname, ic.codenumber, p.projectname
FROM campuses c
INNER JOIN universities u ON u.iduni = c.iduni
INNER JOIN camousspecialization cs ON cs.idcampus = c.idcampus
INNER JOIN speicialization s ON s.idspec = cs.idspec
INNER JOIN projectspecialization ps ON ps.idproject = s.idspec
INNER JOIN projectindustrycode pi On pi.idproject = ps.idproject
INNER JOIN industrycode ic on ic.industrycode = pi.industrycode
INNER JOIN projects p on p.idproject = ps.idproject