how to select data from two tables with join?

honar abubakr

i have three tables

1- room_info with four fields
field_name   data_type    description
----------   ----------    -----------
r_id    number    primary key
r_number       number
r_floor            text

1- student_info with five fields
field_name   data_type    description
----------   ----------    -----------
s_id    number    primary key
s_name         text
s_college       text
s_dept          text
r_id    number    foreign key

3- como_info with five fields
field_name   data_type    description
----------   ----------    -----------
c_id    number    primary key
c_sys        number
c_dosh          number
c_dol             number
r_id number    foreign key

r_id from room_info is foreign key for both como_info and student_info
i want to make select query to select s_name,s_college,c_sys,c_dosh???
i use c# and access 2010

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sanjeeb Lenka13y ago
Accepted answer
try this one

Select student_info.s_name,student_info.s_college,como_info.c_sys,como_info.c_dosh
from (student_info
inner join como_info on como_info.r_id=student_info.r_id)
SUNIL GUTTA13y ago
TY MATE :)
Iftikar Hussain13y ago
it is easily can find out by seeing the table design, as mentioned PK and FK

Regards,
Iftikar
SUNIL GUTTA13y ago
Well nice explanation how can we guess as u guessed that room_info as parent table ?? how did u
Iftikar Hussain13y ago
No, I mean two table are related to one parent table. In your example
room_info is parent table where r_id is PK and
student_info and como_info are child table with relation of r_id as FK

Regards,
Iftikar
SUNIL GUTTA13y ago
well humm ok but the above question states that there are three different tables ?? u mean parent table means the three tables created under same database haa ???
Iftikar Hussain13y ago
you can use FK relationship between two table if you no both table are belongs to one parent table.

Regards,
Iftikar
SUNIL GUTTA13y ago
Iftikar Hussain hie mate

Well same doubt for me in-case of ur query also i.e in ur second INNERJOIN u created a relation between 2 foreign keys :) which is not in practice ..

I finally hope the above question may be wrongly mentioned so i got this little confusion
Iftikar Hussain13y ago
have you tried my solution?

Regards,
Iftikar
SUNIL GUTTA13y ago
Hey i a confused here ..

como_info.r_id=student_info.r_id how could we relate tables using 2 foreign keys mate ?? made be one of this is a primary key [r_id] ???
honar abubakr13y ago
sanjeb thank you for answer ... also thank you eftikar
Iftikar Hussain13y ago
Hi,
Try like this

select student_info.s_name,student_info.s_college,student_info.c_sys,student_info.c_dosh,
room_info.r_number,room_info.r_floor
FROM(room_info
INNER JOIN student_info ON room_info.r_id=student_info.r_id)
INNER JOIN como_info ON student_info.r_id=como_info.r_id

Regards,
Iftikar