CODEWITHSUNDEEP
mysql
philcode101
philcode101

Reputation: 35

MYSQL Search returning wrong and duplicate values

I'm doing a project at university and I seem to be encountering some issues when i'm trying to search to collect some results.

I am trying to display the results which give the StudentName, ModuleName, and DegreeID. When I do this, it appears to be duplicating the values and returning wrong results.

For example - Owen Barnes is only studying Computer Science, not Philosophy yet it is simply returning all values instead of the specified 3 it should. Further, Connor Borne is studying Philosophy yet it is suggesting he is studying every module including those in Computer Science.

I was hoping someone could help me. I'm using 2 tables (ModulesFormDegree & StudiesModules) which are used to link Modules to Degree (using 2 foreign keys) and Students with Modules (also using 2 foreign keys).

I've attached my problem below, if any more data is required please let me know.

Inquiry & Results Description of Tables

Query: 

select StudentName, ModuleName, DegreeID 
from Student, Modules, Degree, StudiesModules, ModulesFormDegree 
where Student.StudentID=StudiesModules.StudentID and 
      Modules.ModuleID=ModulesFormDegree.ModID and 
      Degree.DegreeID=ModulesFormDegree.DegID

Upvotes: 0

Views: 43

Answers (1)

SE1986
SE1986

Reputation: 2750

It's diffcult to say for sure because you have not posted all your table definitions but your query is missing a condition in the where clause which is causing the Cartesian product and can be fixed as follows:

select  StudentName,
        ModuleName,
        DegreeID
from    Student,
        Modules,
        Degree,
        StudiesModules,
        ModulesFormDegree
where   Student.StudentID=StudiesModules.StudentID and
        Modules.ModuleID=ModulesFormDegree.ModID and
        Degree.DegreeID=ModulesFormDegree.DegID and
        StudiesModules.ModuleID = ModulesFormDegree.ModID

however, joining tables on conditions in the WHERE clause is fairly antiquated and superseded using ANSI joins as follows:

SELECT  StudentName,
        ModuleName,
        DegreeID
FROM    StudiesModules sm
        JOIN ModulesFormDegree md
            ON sm.ModuleID = md.ModID
        JOIN Degree d
            On d.DegreeID = md.DegID
        JOIN Modules m
            ON m.ModuleID = md.ModID
        JOIN Student s
            ON s.StudentID = sm.StudentID

Upvotes: 1

Related Questions