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

我有以下表格：
Universities( iduni, uniname )
Campuses( idcampus, iduni, campusname, campusaddress )
Projects( idproject, idcampus, projectname )
IndustryCode( idindustrycode, codenumber, description )
Specialization( idspec, specname )
下面是上面列出的一些表的关联表
CampusSpecialization( idcampus, idspec )
ProjectSpecialization( idproject, idSpec )
ProjectIndustryCode( 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>

这是我迄今为止的声明：

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

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

最佳答案

试试这个：

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