How to fetch a particular record from three tables

Question

Table Name :: Feedback_master

Fields 1. feed_id 2. roll_id 3. batch_id 4. sem_id (semester ID) 5.f_id (faculty Id) 6. sub_id (subject Id) 7. remark. 8. b_id

Table Name :: subject_master

Fields

1. sub_id (subject Id)
2. sub_name (Subject Name0
3. f_id ( Faculty ID)

Table Name :: faculty_master

Fields

1. f_id (Faculty Id)
2. f_name (Faculty Name)
3. l_name (Faculty Name)
4. b_id

This are the three tables. Now I want to fetch the detail from this three table.

I want the output as

f_Name (faculty name), Sub_name (Subject Name ) , and remark (Remark ) when i give the (faculty id) f_id

could some one help me to over come this problem.

Answer 1

Using Objects

Select T1.f_name, T2.sub_name, T3.remark from faculty_master as T1, 
subject_master as T2, Feedback_master as T3 where T1.f_id = 'your faculty_id'
and T1.f_id = T3.f_id and T2.sub_id = T3.sub_id

Answer 2

heu, MySQL I presume?

SELECT f_name, sub_name, remark 
FROM faculty_master
LEFT JOIN subject_master USING(f_id)
LEFT JOIN Feedback_master USING(f_id)
WHERE f_id = the_id_you_want

Answer 3

select fm.f_name, sm.sub_name, remark from faculty_master fm left join sub_master sm on fm.f_id=sm.f_id left join feedback_master fbm on sm.sub_id = fbm.sub_id where fm.f_id= 123

Answer 4

You can build up the query in stages. The first thing is that you're after a list of feedback remarks, so start with this simple select query:

SELECT * FROM Feedback_master

That's listing all the feedback from all over, but you want to limit it to only feedback on a particular faculty, so let's add a Where clause:

SELECT * FROM Feedback_master
  WHERE Feedback_master.f_id = @f_id

Now we've got the right list of records, but the list of fields is wrong. You want the faculty name and subject name, which aren't there in the Feedback_master table; the subject_master and faculty_master tables are linked and assuming that every remark has a subject ID and a faculty ID, we can use a simple inner join to link the tables:

SELECT * FROM Feedback_master
  INNER JOIN subject_master ON Feedback_master.sub_id = subject_master.sub_id
  INNER JOIN faculty_master ON Feedback_master.f_id = faculty_master.f_id
  WHERE Feedback_master.f_id = @f_id

Now it's pulling out all the fields from all three table; this includes all the fields we need, so we can now simply name them in the Select clause:

SELECT
  faculty_master.f_name, subject_master.sub_name, Feeback_master.remark
FROM Feedback_master
  INNER JOIN subject_master ON Feedback_master.sub_id = subject_master.sub_id
  INNER JOIN faculty_master ON Feedback_master.f_id = faculty_master.f_id
WHERE Feedback_master.f_id = @f_id