Hello All, I have a SQL query I need help with:

Here are my tables:

mysql> desc file;
+-------------------------+---------------------+------+-----+---------+----
---+
| Field                   | Type                | Null | Key | Default |
Extra |
+-------------------------+---------------------+------+-----+---------+----
---+
| file_name               | char(255)           |      | PRI |         |
|
| required_security_level | tinyint(1) unsigned |      |     | 0       |
|
| checked_out_by          | char(20)            | YES  |     | NULL    |
|
| date_checked_out        | datetime            | YES  |     | NULL    |
|
+-------------------------+---------------------+------+-----+---------+----
---+
4 rows in set (0.00 sec)

mysql> desc staff;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| username       | char(20)            |      | PRI |         |       |
| last_name      | char(30)            |      |     |         |       |
| first_name     | char(30)            |      |     |         |       |
| security_level | tinyint(1) unsigned |      |     | 0       |       |
+----------------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

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;

Anyway, any help with this query would be greatly appreciated.

Thank You,
Mike Hillyer


---------------------------------------------------------------------
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

Reply via email to