CODEWITHSUNDEEP
sqldatabase
el323
el323

Reputation: 2920

I need some help to write an sql query, I want to find all the instructors teaching courses

There are 3 tables:

ins_id and c_id are foreign and primary keys.

I know that all the instructors teaching courses information will be stored in ins_teach_course, but I want to extract ins_name and c_name from that as well.

I have written this so far, is this correct?

select 
    i.ins_id, i.ins_fname, i.ins_lname, c.c_id, c.c_name
from 
    ins_reg i, course c
where 
    i.ins_id = (select ins_id from ins_teach_course) 
    AND c.c_id = (select c_id from ins_teach_course);

Thank you.

Upvotes: 1

Views: 561

Answers (2)

Marco
Marco

Reputation: 3641

You need something like this:

SELECT I.ins_name, C.c_name
FROM Ins_teach_course IC
JOIN Course C ON C.c_id = IC.c_id
JOIN Instructor I ON I.ins_id = IC.ins_id

Upvotes: 0

Mureinik
Mureinik

Reputation: 311393

Your solution may retrieve uncorrelated results between instructors and courses. In order to correlate them, you could use a join:

SELECT i.ins_id, i.ins_name, c.c_id, c.c_name
FROM   instructor i
JOIN   ins_teach_course itc ON i.ins_id = itc.ins_id
JOIN   course c ON c.c_id = itc.c_id

Upvotes: 1

Related Questions