Assistance needed for SQL query to find instructors and their courses

Courses
1

I’m working with a database that has three tables: Educator (ed_id, ed_name), Subject (s_id, s_name), and Educator_Subject (ed_id, s_id). The ed_id and s_id are both foreign and primary keys.

I want to get the names of educators and the subjects they teach from the Educator_Subject table. But I’m not sure if my query is right. Here’s what I’ve got so far:

SELECT 
    e.ed_id, e.ed_name, s.s_id, s.s_name
FROM 
    Educator e, Subject s
WHERE 
    e.ed_id = (SELECT ed_id FROM Educator_Subject) 
    AND s.s_id = (SELECT s_id FROM Educator_Subject);

Can someone check if this is correct or suggest a better way to do it? I’m pretty new to SQL and could use some help. Thanks!

2

Your current query isn’t quite right, but you’re on the right track. The main issue is that you’re using subqueries in a way that won’t work as intended. Here’s a corrected version using INNER JOINs:

SELECT e.ed_id, e.ed_name, s.s_id, s.s_name
FROM Educator e
INNER JOIN Educator_Subject es ON e.ed_id = es.ed_id
INNER JOIN Subject s ON s.s_id = es.s_id;

This query will join all three tables together, matching educators with their subjects through the Educator_Subject table. It’s more efficient and will give you the correct results.

Remember, when dealing with many-to-many relationships (like educators to subjects), you typically need to join through the intermediate table. The INNER JOIN syntax is generally preferred over the older comma-separated FROM clause for readability and to avoid accidental cross joins.

3

hey ava, ur query needs some tweaking. try using JOINs instead. something like:

SELECT e.ed_name, s.s_name
FROM Educator e
JOIN Educator_Subject es ON e.ed_id = es.ed_id
JOIN Subject s ON s.s_id = es.s_id;

This should get u what u need. lmk if u need more help!

4

Hey there Ava_Books! :wave:

I see you’re diving into the world of SQL joins - fun stuff, right? :smile: The others have given some solid advice, but I’m curious about a few things:

Have you tried running the query yet? What kind of results are you getting?

Also, I’m wondering what you’re planning to do with this data once you’ve got it. Are you building a course catalog or maybe a teacher directory?

One thing that might be cool to add to your query is a count of how many subjects each educator teaches. Something like:

SELECT e.ed_name, COUNT(s.s_id) as subject_count
FROM Educator e
JOIN Educator_Subject es ON e.ed_id = es.ed_id
JOIN Subject s ON s.s_id = es.s_id
GROUP BY e.ed_name;

What do you think? Would that be useful for your project?

Keep us posted on how it goes! SQL can be tricky at first, but you’ll get the hang of it in no time. :blush: