3

This may sound simple and dumb but how can I get the values of a foreign key from a table and displaying it?

I have a table named "subjects" which contains different school subjects and one column of it is a foreign key referencing from table named "faculty".

TABLE subjects
___________________________________
| sub_id | sub_faculty | sub_desc |
|    1   |      2      | PHYSICS  |
|    2   |      3      |   MATH   |
|    3   |      4      | HISTORY  |
|________|_____________|__________|

TABLE faculty
________________________
| fac_id |  fac_name   |
|    2   |    John     |
|    3   |    Mark     |
|    4   |   Johnny    |
|________|_____________|

I firstly wanted to check if data exist in the "subject" table and then proceed on displaying the row values of the foreign key.

I have this not so working query as displays both JOHN and JOHNNY. I was using LIKE as it will be for the search feature of the system so hopefully you can help me out on this.

SELECT * 
FROM subject, faculty 
WHERE subject.sub_desc = 'PHYSICS' 
  AND subject.sub_year = '4' 
  AND faculty.fac_name LIKE '%JOHN%' 
GROUP BY faculty.fac_id
Praveen
  • 8,945
  • 4
  • 31
  • 49
King Soul
  • 33
  • 1
  • 4
  • 3
    what do you want to achieve? to display faculty information for a given subject? – chathux Aug 15 '15 at 10:56
  • Right about that, I want the results to display the row in the table subject and instead of the foreign key, the row values inside the foreign key. – King Soul Aug 15 '15 at 11:21

1 Answers1

4
SELECT * 
FROM subject s
join faculty f 
  on s.sub_faculty = f.fac_id
WHERE s.sub_desc = 'PHYSICS' 
  AND s.sub_year = '4' 
  AND f.fac_name LIKE '%JOHN%'
juergen d
  • 201,996
  • 37
  • 293
  • 362
Praveen
  • 8,945
  • 4
  • 31
  • 49