Page 1 of 1

3 Tables Joining Issue

Posted: Sat Mar 05, 2011 5:54 pm
by t0m0
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

Code: Select all

$sql ="SELECT * FROM projects, clients WHERE projects.client_id = clients.client_id ORDER BY projects.project_id ASC";
I was thinking something along the lines of this

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";
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!

Re: 3 Tables Joining Issue

Posted: Sat Mar 05, 2011 8:03 pm
by Christopher
You probably want to join with ON:

Code: Select all

$sql ="SELECT * FROM clients JOIN projects ON clients.client_id=projects.client_id JOIN files ON projects.project_id=files.project_id WHERE ..."
Use the WHERE to select specific records. You may need to do LEFT, INNER or OUTER joins depending on what your are looking for.

Re: 3 Tables Joining Issue

Posted: Sun Mar 06, 2011 4:21 pm
by t0m0
Hi,

Thanks for your reply.

I have tried this statement ...

Code: Select all

SELECT * FROM clients JOIN projects ON clients.client_id=projects.client_id LEFT JOIN files ON projects.project_id=files.project_id ORDER BY projects.project_id ASC"
However this then stops the project ID from displaying if a project does not have a file attached to it.

I basically require the clients to always be linked to respective projects if the projects exist through a client_id field in the clients table and a client_id field in the projects table, regardless of whether a project has a file associated to it.

Then, if a project exisits, files can be attached to it through the project_id field in the files table and the project_id field in the projects table.

Am I on the right lines with the above statement?

Cheers.

Re: 3 Tables Joining Issue

Posted: Sun Mar 06, 2011 5:33 pm
by califdon
Read http://www.w3schools.com/sql/sql_join.asp, especially the paragraph at the bottom, "Different SQL Joins".