3 Tables Joining Issue
Posted: Sat Mar 05, 2011 5:54 pm
Hi,
I have three tables in my database.
Clients, Projects and Files (upload files)
One client can have multiple projects and one project can have multiple files.
The clients table contains a client_id field (primary key, auto increment), the projects table contains a project_id (primary key auto increment) and a client_id (foreign key).
I have basically set it up so that when a client_id in the projects table matches a client_id in the clients table the two rows in both tables get linked together.
My problem is I now want to get this to work three ways to include my files table.
My code so far is
I was thinking something along the lines of this
Basically I always want the first where clause to always execute, and then if the user has uploaded a file with the project I want MYSQL to link that file with the appropriate project through the project_id fields in projects and files?
Do I need more than one SQL statement to do this? I am stumped!
Cheers!
I have three tables in my database.
Clients, Projects and Files (upload files)
One client can have multiple projects and one project can have multiple files.
The clients table contains a client_id field (primary key, auto increment), the projects table contains a project_id (primary key auto increment) and a client_id (foreign key).
I have basically set it up so that when a client_id in the projects table matches a client_id in the clients table the two rows in both tables get linked together.
My problem is I now want to get this to work three ways to include my files table.
My code so far is
Code: Select all
$sql ="SELECT * FROM projects, clients WHERE projects.client_id = clients.client_id ORDER BY projects.project_id ASC";Code: Select all
$sql ="SELECT * FROM projects, clients, files WHERE projects.client_id = clients.client_id AND (files.project_id = projects.project_id) ORDER BY projects.project_id ASC";Do I need more than one SQL statement to do this? I am stumped!
Cheers!