Need help for my tables

[jemz]

Hi, I just want to ask because i am confuse with my three tables,..my question is..Is it possible to use the join query like the inner join,left join,outer join.,etc...in the 3 tables, as i know that when we use the inner join it must be the pk and fk,example:..

select e.emp_no, e.f_name,e.l_name,p.proj_no,p.res_empno
from employee e join project p
on e.emp_no = p.res_empno

the emp_no is pk for the employee table and the res_empno is the fk for the project table.

but what if the design of my table is something like this

table1
std_idno = pk
std_fname
std_lname
std_classno

table2
rm_stdno = pk
rm_stdclassno
rm_size
rm_description

I want that if the student has the same classno in the table2 i want to display the rm_size and the rm_description,student name and idno.

Is it possible to use the join query on this,can i populate the exact data?please help me on this I am really confuse....


I am using wamp and i create the database in the phpmyadmin.


Thank you in advance

[cpradio]

First off, here is a brief definition of each join
http://www.firebirdfaq.org/faq93/

Second, based on what you are asking, you want to show students and their associated class so long as that class exists in table2.

So I believe you could theoretically use any of the joins for this scenario, but it seem inner join may be best suited.

SELECT rm_size, rm_description, std_fname, std_lname, std_idno FROM table1 AS Student INNER JOIN table2 AS Room ON Student.std_classno = Room.rm_stdclassno

[jemz]

First off, here is a brief definition of each join
http://www.firebirdfaq.org/faq93/

Second, based on what you are asking, you want to show students and their associated class so long as that class exists in table2.

So I believe you could theoretically use any of the joins for this scenario, but it seem inner join may be best suited.

SELECT rm_size, rm_description, std_fname, std_lname, std_idno FROM table1 AS Student INNER JOIN table2 AS Room ON Student.std_classno = Room.rm_stdclassno

Hi cpradio, Thank you so much for the reply,,....I forgot to put that the Student.std_classno and Room.rm_stdclassno have different data types and length associated...

[cpradio]

Okay, but do the values match up or what would you need to do to make the columns match up?

Can you give sample data stored in both tables and then the desired returned result you want to see?

[jemz]

Okay, but do the values match up or what would you need to do to make the columns match up?

Can you give sample data stored in both tables and then the desired returned result you want to see?

Hi cpradio,..I apologize for my last reply,I mean to say that Student.std_classno and Room.rm_stdclassno is not a foreign key and also it is not a primary key...but they both having the same values and datatypes...my question is this,is it okay to use inner join even they are not pK and Fk?can i still get the right data?.

[cpradio]

Yes, you can still get the right data. It would be nice if they had some sort of relationship to verify data integrity, but that is the only issue the fk relationship would resolve. It would prevent both tables from having invalid data.

[jemz]

Yes, you can still get the right data. It would be nice if they had some sort of relationship to verify data integrity, but that is the only issue the fk relationship would resolve. It would prevent both tables from having invalid data.

Hi thank you for this, but for example the table is already created how can i know that this table have FK relationship to other tables ?by the way i do this in phpmyadmin.

[cpradio]

I don't use phpmyadmin a lot, but maybe this video will be helpful
http://www.youtube.com/watch?v=IdQGFZwP7Xc

[jemz]

I don't use phpmyadmin a lot, but maybe this video will be helpful
http://www.youtube.com/watch?v=IdQGFZwP7Xc

Hi, cprdio thank you for helping me, Okay i will watch this video.and i will write back to you again if i get in trouble.


More power to you always.