What is the correct JOIN to use for 3 tables for output

[geekisthenewsexy]

hi guys, i have 3 tables namely course,year and block.

>>in table course, i have 2 fields (c_id, c_name) where c_id is primary and auto-incremented.

>>in table year, i have 3 fields (y_id, c2_id, year) where c2_id is foreign and y_id is unique and auto-incremented.

>>in table block, i have 3 fields also (b_id, y2_id, block) where y2_id is foreign, b_id unique..

now, I'm working on some project that gets input from the user; course,year and block.

 

okay so i tried inputting record couple of times and on the database:

table course:

_______________

c_id | c_name

1    | BEED

2    | BSA

 

table year:

_____________________

y_id | c_id | year

1    | 1    | First year

2    | 1    | Second year

3    | 2    | First year

4    | 2    | Second year

 

table block:

___________________

b_id | y_id | block

1    | 1    | ED1-A

2    | 1    | ED1-B

3    | 2    | ED2-A

4    | 3    | AC1-A

5    | 3    | AC1-B

6    | 4    | AC2-A

 

now, i tried querying like

$result = mysql_query("SELECT DISTINCT c_name,block,year FROM (course LEFT JOIN year ON course.c_id=year.c2_id)RIGHT OUTER JOIN block ON block.y2_id=year.c2_id") or die(mysql_error()); 

(i don't know if this is the right query) but this gives me jumbled output..

 

my problem is what kind of query will i use to be able to make my output like this

_________________________

COURSE | YEAR            | BLOCK

BEED      | First year      | ED1-A

                                    | ED1-B

              | Second year | ED2-A

BSA        | First year      | AC1-A

                                    | AC1-B

              |Second year  | AC2-A

 

..i badly need your help here guys..i'm seriously losing my mind here.. :-\ :'(

                                   

[fenway]

Don't mix LEFT and RIGHT.

[geekisthenewsexy]

hi, thanks fenway for your response.

ok..what shoukd i use then? i'm not really good in mysql stuff..

[Buddski]

This might help you in the right direction..

 

SELECT `c_name`, `year`, `block` FROM `course`
Inner Join `year` ON `year`.`c_id` = `course`.`c_id`
Inner Join `block` ON `block`.`y_id` = `year`.`y_id`
ORDER BY `c_name`,`year`

 

It should output something workable within PHP at least *crosses fingers*

Like so.

BEED  First Year   ED1-A

BEED  First Year   ED1-B

BEED  Second Year   ED2-A

BSA    First Year   AC1-A

BSA    First Year   AC1-B

BSA    Second Year   AC2-A

[geekisthenewsexy]

hi, thanks for the response buddski. 

It should output something workable within PHP at least *crosses fingers*

Like so.

so does that mean involving a php code to display

_________________________

COURSE | YEAR            | BLOCK

BEED      | First year      | ED1-A

                                    | ED1-B

              | Second year | ED2-A

BSA        | First year      | AC1-A

                                    | AC1-B

              |Second year  | AC2-A

if what you gave me will work?

[Buddski]

Yeah I would just use PHP do filter it all out into the right display.

[Waddy]

Hi,

 

I spent ages trying to find the correct info for this myself. This is what i worked from and it works. My tables have foreign id's with relationship to primary key.

 

Example:

 

$query="SELECT * FROM table1,table2,table3 WHERE table1.t1_ID=table2.t2_ID AND table2.t2_ID=table3.t3_ID";

 

Hope it helps

[geekisthenewsexy]

hey waddy, thanks. i'll try it out 

[geekisthenewsexy]

hi there!

hey Waddy, you just saved me. 

it now displays correctly thanks to you.

keep it up!