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
First off, here is a brief definition of each join
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…
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?.
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.