mysql error! Is it possible to use table variables in mysql?-CodePudding

I'm learning mysql and I'm testing assigning table variables for retrieving information from the same table. I have a simple table of students and I'd like pairs of students who have the same GPA (from the same table -student-. So I assigned a variable for student 1 (S1) and another one for S2, Im expecting pairs of different students with the same GPA but I get something else; another thing to mention is that if I export the query results to a csv file, only S1 data is on the table, S2 information is blank (empty cells), do variables work differently in mysql?

This is the query from the SQL course (that I'm not able to replicate in mysql)

SELECT s1.sID, s1.sName, s1.GPA, s2.sID, s2.sName, s2.GPA
FROM student s1, student s2
WHERE s1.GPA = s2.GPA AND s1.sID < s2.sID;

here are the tables and data:

--tables
CREATE TABLE college(
    cName VARCHAR(15),
    state VARCHAR(15),
    enrollment INT,
    PRIMARY KEY(cName)
);
CREATE TABLE student(
    sID INT,
    sName VARCHAR(40),
    GPA DECIMAL(3,1),
    sizeHS INT,
    PRIMARY KEY(sID)
);

CREATE TABLE application(
    sID INT,
    cName VARCHAR(20),
    major VARCHAR(20),
    decision VARCHAR(1),
    PRIMARY KEY(sID, cName, major)
);

--college data injection
INSERT INTO college VALUES('UNAM', 'Mexico City', 360000);
INSERT INTO college VALUES('UAM', 'Mexico City', 250000);
INSERT INTO college VALUES('Tec Culiacan', 'Sinaloa', 9000);
INSERT INTO college VALUES('UAS', 'Sinaloa', 110000);
INSERT INTO college VALUES('UACH', 'Chihuahua', 65000);
INSERT INTO college VALUES('Tec Monterrey', 'Nuevo Leon', 7500);

--student data injection
INSERT INTO student VALUES(9832, 'Juan Perez', 85.0, 3000);
INSERT INTO student VALUES(0219, 'Maria Gonzalez', 92.0, 9000);
INSERT INTO student VALUES(1683, 'Pedro Sanchez', 79.0, 13000);
INSERT INTO student VALUES(8329, 'Pilar Madrid', 83.0, 1500);
INSERT INTO student VALUES(0921, 'Eva Quintero', 80.0, 7900);
INSERT INTO student VALUES(9816, 'Francisco Hernandez', 96.0, 6500);

--application data injection
INSERT INTO application VALUES(9816, 'UNAM', 'CS', 'Y');
INSERT INTO application VALUES(9816, 'UAM', 'EE', 'Y');
INSERT INTO application VALUES(8329, 'Tec Monterrey', 'MGMT', 'N');
INSERT INTO application VALUES(8329, 'UNAM', 'Admin', 'N');
INSERT INTO application VALUES(1683, 'UAM', 'Biology', 'N');
INSERT INTO application VALUES(1683, 'UAS', 'Biology', 'Y');
INSERT INTO application VALUES(0219, 'Tec Culiacan', 'CS', 'Y');
INSERT INTO application VALUES(0219, 'UACH', 'CS', 'Y');
INSERT INTO application VALUES(0921, 'UAS', 'Industrial', 'Y');
INSERT INTO application VALUES(0921, 'Tec Culiacan', 'Mechatronic', 'N');
INSERT INTO application VALUES(9832, 'UAM', 'Arch', 'Y');
INSERT INTO application VALUES(0921, 'UACH', 'Industrial', 'N');

And this is the expected result from the query:

CodePudding user response：

Your query works fine but first make sure you have students with same GPA in the student table and then run the query again.

--student data injection
INSERT INTO student VALUES(9832, 'Juan Perez', 85.0, 3000);
INSERT INTO student VALUES(0219, 'Maria Gonzalez', 92.0, 9000);
INSERT INTO student VALUES(1683, 'Pedro Sanchez', 96.0, 13000);
INSERT INTO student VALUES(8329, 'Pilar Madrid', 92.0, 1500);
INSERT INTO student VALUES(0921, 'Eva Quintero', 80.0, 7900);
INSERT INTO student VALUES(9816, 'Francisco Hernandez', 96.0, 6500);

Result from the querey: