What is your question/what problem are you having?
--
Joe
On Aug 10, 2009, at 4:28 PM, Ralph Kessel wrote:
> I've been wrestling with a mysql script as part of a PHP script
> found in a text book:
>
> Message d'erreur : Unknown column 'a.type_id' in 'on clause'
> SELECT a.name, a.type_id, b.title,
> b.description, b.content_date,
> b.create_date, b.created_by,
> b.last_upd_date, b.last_upd_by,
> c.name as dept_name, content_id
> FROM Content_Type a, Department c
> LEFT OUTER JOIN Content b on
> a.type_id = b.content_type
> and a.type_id = b.content_type
> and b.dept_id = 3
> and b.content_type = 4
> WHERE c.dept_id = 3
> ORDER BY content_date DESC
>
> table structure
> DROP TABLE IF EXISTS Department;
>
> CREATE TABLE Department (
> dept_id SERIAL,
> name VARCHAR(255) NOT NULL,
> description VARCHAR(255) NOT NULL,
>
> PRIMARY KEY (dept_id)
> );
>
> DROP TABLE IF EXISTS Content_Type;
>
> CREATE TABLE Content_Type (
> type_id SERIAL,
> name VARCHAR(255) NOT NULL,
>
> PRIMARY KEY (type_id)
> );
>
> DROP TABLE IF EXISTS Content;
>
> CREATE TABLE Content (
> content_id SERIAL,
> dept_id INT NOT NULL,
> content_type INT NOT NULL,
> title VARCHAR(255) NOT NULL,
> description TEXT NOT NULL,
> content_date DATE,
> creation_date TIMESTAMP
> DEFAULT now() NOT NULL,
> created_by VARCHAR(255) NOT NULL,
> last_upd_date TIMESTAMP NOT NULL,
> last_upd_by VARCHAR(255) NOT NULL,
>
> PRIMARY KEY(content_id)
> );
>
> DROP TABLE IF EXISTS Dept_User;
>
> CREATE TABLE Dept_User (
> user_name VARCHAR(255)
> UNIQUE NOT NULL,
> dept_id INT NOT NULL,
> first_name VARCHAR(255) NOT NULL,
> last_name VARCHAR(255) NOT NULL,
> password VARCHAR(255) NOT NULL,
> creation_date TIMESTAMP
> DEFAULT now() NOT NULL,
> email VARCHAR(255),
>
> PRIMARY KEY(user_name)
> );
>
> DROP TABLE IF EXISTS Content_Download;
>
> CREATE TABLE Content_Download (
> download_id SERIAL,
> content_id INT(6) NOT NULL,
> file_name VARCHAR(255) NOT NULL
> );
>
> INSERT Content_Type (name) values ("News");
> INSERT Content_Type (name) values ("Events");
> INSERT Content_Type (name) values ("FAQ");
> INSERT Content_Type (name) values ("Meeting Materials");
>
> INSERT Department (name, description)
> values ("Human Resources", "
> Bringing the right people together to get the job done.");
>
> INSERT Department (name, description)
> values ("Sales",
> "Our experienced sales team markets our great products to the
> public.");
>
> INSERT Department (name, description)
> values ("Distribution", "We get the goods to the customers.");
>
> INSERT Department (name, description)
> values ("Information Techology",
> "Building the applications that give us the edge.");
> I would appreciate assistance
>
> [Non-text portions of this message have been removed]
>
>
[Non-text portions of this message have been removed]
