Q:

Specific MYSQL view with null values

I'm having trouble creating a view for one of my MYSQL assignments.  I understand how to create a view technically, as in, the commands to do so.  (I have already done a few other different views for this assignment)  My problem is with how to design this particular view... I don't know how to with the knowledge I have/The way I designed my tables.  
So, I have 2 relevant tables(There are 2 others but I don't think they are needed for this problem):  Attendance and Scholar.  I need to create a view where all scholars are listed as well as the date where they were an invited speaker.  However, if they were never an Invited Speaker, the date should have a null value shown.  So I need to select First Name and LastName from Scholar and ADate from Attendance.  Attendance has the column AttendanceType that can be either Invited Speaker or Chairman.  Attendance also has the foreign key LName, relating to LastName, and ADate obviously.  I can't conceptually think of have to do this, I thought that using a join, which I'm not that experienced with would be the right choice but it didn't work...
Here's what I attempted
CREATE VIEW InvitedScholars
AS SELECT FirstName,LastName,ADate
FROM Scholar LEFT JOIN Attendance ON AttendanceType='Invited Speaker'
WHERE Lname=LastName;

This only gave me Invited Speakers, not all Scholars... I don't know how to progress... any advice would be appreciated.

A:

You need to do your left join on the Last Name (assuming this is your key on both tables). See the SQL below:
CREATE VIEW InvitedScholars
AS SELECT FirstName,LastName,ADate
FROM Scholar LEFT JOIN Attendance ON Scholar.LastName = Attendance.LName
AND Attendance.AttendanceType = 'Invited Speaker';

