> What I want to do is display to the user a list of files that > require a > security level that is less than or equal to the user's > security level, with > formatted last name and first name and date if the file is > checked out. > > Checked out by is a foreign key refering to username in the > staff table, and > the security level and required security level are also related. > > This is my attempt: > > SELECT file_name, CONCAT(last_name, ', ', first_name) AS name, > DATE_FORMAT(date_checked_out,'%b %D, %Y (%r)') AS date > FROM file LEFT JOIN staff ON file.checked_out_by = staff.username > WHERE staff.username = 'mike' > AND staff.security_level > file.required_security_level; >
Your query won't do what you want because you are only selecting files that are checked out by mike (staff.username='mike'). You'll need two queries to do this. First, get the user's security level and store it in a user variable: select @SecLev:=security_level from staff where username='mike'; Then, you can do your left join as you've described above: SELECT file_name, CONCAT(last_name, ', ', first_name) AS name, DATE_FORMAT(date_checked_out,'%b %D, %Y (%r)') AS date FROM file LEFT JOIN staff ON file.checked_out_by = staff.username WHERE @SecLev > file.required_security_level; By the way, when you're doing a left join, you should never include a column from the right table in your where clause. This is because the whole reason for doing a left join is to include rows that have no match in the right table, and any such rows will have all columns in the right table set to NULL. So, if you do a where condition on a column in the right table, you've just negated the effect of your left join (unless you're checking for IS NULL). --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
