Geek asks and answers

SQL SELECT to output data from multiple tables with foreign keys and associative relationships

I have the following tables:

  • Universities( iduni, uniname )
  • Campuses( idcampus, iduni, campusname, campusaddress )
  • Projects( idproject, idcampus, projectname )
  • IndustryCode( idindustrycode, codenumber, description )
  • Specialization( idspec, specname )

The following are associative tables for some of the above tables.

  • CampusSpecialization( idcampus, idspec )
  • ProjectSpecialization( idproject, idSpec )
  • ProjectIndustryCode( idproject,idindustrycode )

And I want to generate an XML file with the following structure via PHP, but I can’t define an operator SELECTto pull data from the database. The data are campuses, universities, projects, industry codes, and specializations for each project, so when I can filter the results in my small application later based on this related data.

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

This is my SQL status so far:

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

EDIT:

Projects may have one or more codes and industries.

+3
source share
2 answers

try the following:

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
+1

, , codeNumber specName.

, . , specNames . xml , . . .. (3) "Project A", SELECT (3) .

SELECT  c.idCampus
        , c.campusName
        , c.campusAddress
        , u.uniName
        , p.projectName
        , s.specName as ProjectSpecName
        , ic.codeNumber AS ProjectCodeNumber
FROM    Campuses c
            INNER JOIN Universities u ON u.iduni = c.iduni
            INNER JOIN Projects p ON p.idcampus = c.idcampus
            INNER JOIN ProjectSpecialization ps ON ps.idproject = p.idproject
            INNER JOIN Specialization s ON s.idspec = ps.idspec
            INNER JOIN ProjectIndustryCode pic ON pic.idproject = p.idproject
            INNER JOIN IndustryCode ic ON ic.idindustrycode = pic.idindustrycode

: , php. xml php. group_concat SELECT, . php xml.

SELECT  c.idCampus
        , c.campusName
        , c.campusAddress
        , u.uniName
        , p.projectName
        , GROUP_CONCAT(s.specName SEPARATOR '|') as SpecNameList
        , GROUP_CONCAT(ic.codeNumber SEPARATOR '|') AS CodeNumberList
       ...
0

More articles:


All Articles