how to select data from two tables with join?

Question

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

Sanjeeb Lenka · Answer

try this one Select student_info.s_namestudent_info.s_collegecomo_info.c_syscomo_info.c_dosh from (student_info inner join como_info on como_info.r_id=student_info.r_id)

Iftikar Hussain · Answer

it is easily can find out by seeing the table design as mentioned PK and FK Regards Iftikar

Iftikar Hussain · Answer

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 GUTTA · Answer

TY MATE :)

SUNIL GUTTA · Answer

Well nice explanation how can we guess as u guessed that room_info as parent table ?? how did u

SUNIL GUTTA · Answer

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 Hussain · Answer

you can use FK relationship between two table if you no both table are belongs to one parent table. Regards Iftikar

SUNIL GUTTA · Answer

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 Hussain · Answer

have you tried my solution? Regards Iftikar

SUNIL GUTTA · Answer

Hey i a confused here .. como_info.r_id=student_info.r_id how could we relate tables using 2 foreign key s mate ?? made be one of this is a primary key [r_id] ???

honar abubakr · Answer

sanjeb thank you for answer ... also thank you eftikar

Iftikar Hussain · Answer

Hi Try like this select student_info.s_namestudent_info.s_collegestudent_info.c_sysstudent_info.c_dosh room_info.r_numberroom_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