Query Question - multiple inner joins?

[Viktig609]

I have searched the forum for a solution to this problem, but perhaps I am not using the correct terms so apologies in advance if this has been solved many times before (which I'm sure it has). I have a very basic grasp of mySQL and php, but this I think has outstretched my meagre knowledge 🙂

What I am trying to build is this- I have a list of research projects, their titles, synopses, the year that the project started, the year it ended and the researchers involved.

I have 3 tables - projects, years and researchers.

Projects has the following fields:

proj_id - primary key
proj_title - title
proj_synopsis - synopsis
date_from - year project started (key for the years table)
date_to - year project ended (key for the years table)
researcher01 (key for researchers table)
researcher02 (key for researchers table)

There can be as many as 10 researchers participating on a project but I only included two in the example above.

the years table just has the fields year_id and years, similarly the researchers table only has the fields researcher_id and researcher

I need some help structuring the query so that it is able to use the same table many times to get different pieces of information - e.g list the names of 5 researchers. Ideally I would like the results displayed -

proj_title
date_from - date_to
researcher01, researcher02, researcher03
proj_synopsis

At the moment if I can get the query to work at all it can come out with some strange results like:

proj_title
date_from - date_from
researcher01, researcher01, researcher01

and then it will repeat the same project title with the following year and so on. I have managed table joins in the past so know about using aliases etc but these were only asking for one piece of information from each table rather than using the key multiple times to get several different results from the same table.

Would something like this be along the right lines?

"SELECT p.proj_id, p.proj_title, p.proj_synopsis, p.date_from, p.date_to,
p.researcher01, p.researcher02, y.year, r.researchers 
FROM projects as p 
INNER JOIN years as y ON (p.date_from=y.year_id)  

INNER JOIN researchers as r ON (p.researcher01=r.res_id) 
ORDER BY p.date_from";

and could I repeat joins to find each different researcher or is there a better way?

[jheitz]

Hi,

There is a design problem with your tables. You really don't WANT to create X fields in the Project table for each of the X researchers that might participate on it. This is for 3 reasons mainly:

1) You are limiting the number of researchers per project
2) Any change in the number of researches will imply a change in the table, something you don't really want later on in your project
3) Your queries will become quite complex, cumbersome and inefficient.

So, what you want is to remove any reference to a researcher on the project table and create a new table called:

RESEARCHES_ON_PROJECT

That holds a Foreign Key to the Project and Research tables... I am sure you get the picture and hope you can move on from here...

[Viktig609]

sees light Of course, that makes a lot of sense - thank you 😃 That actually helps with a few other database questions too. Thanks again.