Order By...problem with coursework


#1

Doing my coursework and wrote the following database:

DROP DATABASE IF EXISTS canary;
CREATE DATABASE canary;
USE canary;
 
  CREATE TABLE member(
  memberID INT AUTO_INCREMENT,
  firstName VARCHAR(40),
  lastName VARCHAR(40),
  grade char, -- j, junior, s, senior
  PRIMARY KEY(memberID)
);
  
  CREATE TABLE series(
  seriesID INT AUTO_INCREMENT,
  seriesName VARCHAR(40),
  seriesYear INTEGER,
  PRIMARY KEY(seriesID)
);
  
CREATE TABLE race(
  raceID INT AUTO_INCREMENT,
  seriesID INT,
  raceName VARCHAR(40),
  raceDate DATE,
  FOREIGN KEY series_key(seriesID) REFERENCES series(seriesID),
  PRIMARY KEY(raceID),
);
CREATE TABLE competitor(
  competitorID INT AUTO_INCREMENT,
  memberID INT,
  raceID INT,
  position INT,
  FOREIGN KEY race_key(raceID) REFERENCES race(raceID),
  FOREIGN KEY member_key(memberID) REFERENCES member(memberID),
  PRIMARY KEY(competitorID),
);

CREATE TABLE course(
    courseID INT AUTO_INCREMENT,
    courseName VARCHAR(50),
    courseLevel INT,
    PRIMARY KEY(courseID)
);

CREATE TABLE enrolment(
  enrolmentID INT AUTO_INCREMENT,
  memberID INT, 
  courseID INT,
  FOREIGN KEY course_key(courseID) REFERENCES course(courseID),
  FOREIGN KEY member_key(memeberID) REFERENCES memner(memberID)
  PRIMARY KEY(enrolmentID)
);

INSERT INTO series VALUES(NULL, 'BritishSeries', 2020);
INSERT INTO series VALUES(NULL, 'EuropeanSeries', 2020);
INSERT INTO series VALUES(NULL, 'InternationalSeries', 2020);

INSERT INTO course VALUES(NULL, 'WhiteWaterRafting', 2);
INSERT INTO course VALUES(NULL, 'BasicTraining', 1);
INSERT INTO course VALUES(NULL, 'ProfessionalTraining', 3);

INSERT INTO race VALUES (NULL, 1, 'Qualifiers', '2018-04-12')
INSERT INTO race VALUES (NULL, 1, 'Regionals', '2019-05-16')
INSERT INTO race VALUES (NULL, 2, 'Nationals', '2019-07-19')
INSERT INTO race VALUES (NULL, 3, 'Internationals', '2019-09-21')

INSERT INTO member VALUES(NULL, 'Jimmy', 'Smith', senior);
INSERT INTO member VALUES(NULL, 'Macky', 'Gee', junior);
INSERT INTO member VALUES(NULL, 'Tempa', 'T', junior);
INSERT INTO member VALUES(NULL, 'Graeme', 'Maigai', senior);
INSERT INTO member VALUES(NULL, 'Willy', 'Nelson', junior);

SELECT * FROM member;

DELETE FROM race WHERE raceID='3';

INSERT INTO enrolmemnt VALUES(NULL, 1, 2);
 -- Member 1
INSERT INTO competitor VALUES(NULL, 1, 2, 3);
INSERT INTO competitor VALUES(NULL, 1, 1, 1);
INSERT INTO competitor VALUES(NULL, 1, 4, 5);

-- Member 2
INSERT INTO competitor VALUES(NULL, 2, 2, 2);
INSERT INTO competitor VALUES(NULL, 2, 1, 3);
INSERT INTO competitor VALUES(NULL, 2, 4, 8);

-- Member 3
INSERT INTO competitor VALUES(NULL, 3, 2, 1);
INSERT INTO competitor VALUES(NULL, 3, 1, 4);
INSERT INTO competitor VALUES(NULL, 3, 4, 3);

-- Member 4
INSERT INTO competitor VALUES(NULL, 4, 2, 5);
INSERT INTO competitor VALUES(NULL, 4, 1, 2);

The question is:

8. List the results of all races in which a given member has participated, use member first name and last name to specify the member. The result should include member first name, last name and grade, series name and year, race name, date and position. The results should be ordered by their position, best position first. If the member has been entered in a race but not yet participated the race should not be included in the list. IDs should not be shown.

So I wrote the code:

SELECT firstName, lastName, grade, seriesName, seriesYear, raceName, raceDate, position FROM member, series, race, competitor

WHERE competitor.memberID = member.memberID ORDER BY position;

But it outputted all the positions as 1 and 2 instead of the race positions.
38

Please help me!


#2

After fixing your creation statement, you are getting exactly what is there, and exactly what you asked the database.

https://www.db-fiddle.com/f/3EFGM4W9KX6qd2jtStmFKo/0

There are more positions, you just don’t have them all listed. So, you need to think about how to orginise the table to get the data you want displayed correctly. Currently, the highest priority is position, so, you will get everything ordered for all the first positions, then all the second, then all the third. Seems you would want another ordering of the race maybe?


#3

Where I put position, I wanted it to represent it with the position they finished in the race which I set as 3 in this example:

INSERT INTO competitor VALUES(NULL, 1, 2, 3);


#4

Use a real join, not the select you have. You need to actually link the tables rather than relying on the engine to do it for you.

Then play with the order by clause.

Your Where clause is redundant, that’s how you join, not how you do the where statement.


#5

Schema (MySQL v5.6)

  CREATE TABLE member(
  memberID INT AUTO_INCREMENT,
  firstName VARCHAR(40),
  lastName VARCHAR(40),
  grade char, 
  PRIMARY KEY(memberID)
)engine=InnoDB;
  
  CREATE TABLE series(
  seriesID INT AUTO_INCREMENT,
  seriesName VARCHAR(40),
  seriesYear INTEGER,
  PRIMARY KEY(seriesID)
)engine=InnoDB;
  
CREATE TABLE race(
  raceID INT AUTO_INCREMENT,
  seriesID INT,
  raceName VARCHAR(40),
  raceDate DATE,
  FOREIGN KEY series_key(seriesID) REFERENCES series(seriesID),
  PRIMARY KEY(raceID)
)engine=InnoDB;

CREATE TABLE competitor(
  competitorID INT AUTO_INCREMENT,
  memberID INT,
  raceID INT,
  position INT,
  FOREIGN KEY race_key(raceID) REFERENCES race(raceID),
  FOREIGN KEY member_key(memberID) REFERENCES member(memberID),
  PRIMARY KEY(competitorID)
)engine=InnoDB;

CREATE TABLE course(
    courseID INT AUTO_INCREMENT,
    courseName VARCHAR(50),
    courseLevel INT,
    PRIMARY KEY(courseID)
)engine=InnoDB;

CREATE TABLE enrolment(
  enrolmentID INT AUTO_INCREMENT,
  memberID INT, 
  courseID INT,
  FOREIGN KEY course_key(courseID) REFERENCES course(courseID),
  FOREIGN KEY member_key(memberID) REFERENCES member(memberID),
  PRIMARY KEY(enrolmentID)
)engine=InnoDB;

INSERT INTO series VALUES(NULL, 'BritishSeries', 2020);
INSERT INTO series VALUES(NULL, 'EuropeanSeries', 2020);
INSERT INTO series VALUES(NULL, 'InternationalSeries', 2020);

INSERT INTO course VALUES(NULL, 'WhiteWaterRafting', 2);
INSERT INTO course VALUES(NULL, 'BasicTraining', 1);
INSERT INTO course VALUES(NULL, 'ProfessionalTraining', 3);

INSERT INTO race VALUES (NULL, 1, 'Qualifiers', '2018-04-12');
INSERT INTO race VALUES (NULL, 1, 'Regionals', '2019-05-16');
INSERT INTO race VALUES (NULL, 2, 'Nationals', '2019-07-19');
INSERT INTO race VALUES (NULL, 3, 'Internationals', '2019-09-21');

INSERT INTO member VALUES(NULL, 'Jimmy', 'Smith', 'senior');
INSERT INTO member VALUES(NULL, 'Macky', 'Gee', 'junior');
INSERT INTO member VALUES(NULL, 'Tempa', 'T', 'junior');
INSERT INTO member VALUES(NULL, 'Graeme', 'Maigai', 'senior');
INSERT INTO member VALUES(NULL, 'Willy', 'Nelson', 'junior');

SELECT * FROM member;

DELETE FROM race WHERE raceID='3';

INSERT INTO enrolment VALUES(NULL, 1, 2);
 -- Member 1
INSERT INTO competitor VALUES(NULL, 1, 2, 3);
INSERT INTO competitor VALUES(NULL, 1, 1, 1);
INSERT INTO competitor VALUES(NULL, 1, 4, 5);

-- Member 2
INSERT INTO competitor VALUES(NULL, 2, 2, 2);
INSERT INTO competitor VALUES(NULL, 2, 1, 3);
INSERT INTO competitor VALUES(NULL, 2, 4, 8);

-- Member 3
INSERT INTO competitor VALUES(NULL, 3, 2, 1);
INSERT INTO competitor VALUES(NULL, 3, 1, 4);
INSERT INTO competitor VALUES(NULL, 3, 4, 3);

-- Member 4
INSERT INTO competitor VALUES(NULL, 4, 2, 5);
INSERT INTO competitor VALUES(NULL, 4, 1, 2);

Query #1

SELECT 
	firstName
    , lastName
    , grade
    , seriesName
    , seriesYear
    , raceName
    , raceDate
    , position 
FROM 
	member m
    INNER JOIN competitor c
    ON m.memberID = c.memberID
    INNER JOIN race r
    ON c.raceID = r.raceID
    INNER JOIN series s
    ON r.seriesID = s.seriesID
firstName lastName grade seriesName seriesYear raceName raceDate position
Jimmy Smith s BritishSeries 2020 Qualifiers 2018-04-12 1
Graeme Maigai s BritishSeries 2020 Qualifiers 2018-04-12 2
Macky Gee j BritishSeries 2020 Qualifiers 2018-04-12 3
Tempa T j BritishSeries 2020 Qualifiers 2018-04-12 4
Tempa T j BritishSeries 2020 Regionals 2019-05-16 1
Macky Gee j BritishSeries 2020 Regionals 2019-05-16 2
Jimmy Smith s BritishSeries 2020 Regionals 2019-05-16 3
Graeme Maigai s BritishSeries 2020 Regionals 2019-05-16 5
Tempa T j InternationalSeries 2020 Internationals 2019-09-21 3
Jimmy Smith s InternationalSeries 2020 Internationals 2019-09-21 5
Macky Gee j InternationalSeries 2020 Internationals 2019-09-21 8

View on DB Fiddle