Question 21630 in Mysql Question posted in
The official MySQL documentation can be found here.

Filtering Dates in mySQL

Just started learning SQL and need help on the Feb 1, 2020 part.

The database has three tables for tracking horse-riding lessons:

Horse with columns:

ID - primary key
RegisteredName
Breed
Height
BirthDate

Student with columns:

ID - primary key
FirstName
LastName
Street
City
State
Zip
Phone
EmailAddress

Lesson Schedule with columns:

HorseID - partial primary key, foreign key references Horse(ID)
StudentID - foreign key references Student(ID)
LessonDateTime - partial primary key

Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student’s first and last names, and the horse’s registered name. Order the results in ascending order by lesson date/time, then by the horse’s registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results.

Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.

This is what I have; not sure of the correct way to go about it. I’ve tried using WHERE and AND but get an error or only all NULL first/last names in the table.

SELECT LessonDateTime, FirstName, LastName, RegisteredName
FROM LessonSchedule
LEFT JOIN Student ON LessonSchedule.StudentID = Student.ID
INNER JOIN Horse ON LessonSchedule.HorseID = Horse.ID
ORDER BY LessonDateTime, RegisteredName;
Tags:

2

Answers


  1. 0

    Please try this.
    I think it will work.

    SELECT LessonDateTime, FirstName, LastName, RegisteredName
FROM LessonSchedule
LEFT JOIN Student ON LessonSchedule.StudentID = Student.ID
INNER JOIN Horse ON LessonSchedule.HorseID = Horse.ID
WHERE LessonDateTime = '2020-02-01'
ORDER BY LessonDateTime, RegisteredName;
    Login or Signup to reply.
  2. 0

    With what Barmar said, final query should look like:

    SELECT LessonDateTime, FirstName, LastName, RegisteredName FROM LessonSchedule LEFT JOIN Student ON LessonSchedule.StudentID = Student.ID INNER JOIN Horse ON LessonSchedule.HorseID = Horse.ID WHERE DATE(LessonDateTime) = '2020-02-01' ORDER BY LessonDateTime, RegisteredName;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search