CODEWITHSUNDEEP
mysqlsql
Undry
Undry

Reputation: 447

Selecting from the table that does many to many

I have the following SQL

SELECT themes.theme_code, themes.name, course_themes.course_code FROM themes LEFT JOIN course_themes ON themes.theme_code=course_themes.theme_code

It produces the table enter image description here

As you probably understood I have the table that connect many Courses to many Themes, as you see in the table the Theme with theme_code 2 is connected to three different Courses (1,2,3), one Theme (3-code) is not connected to any course.

Is there a way, without using arrays in PHP, only in SQL to select only those Themes that are not linked to specific Course, e.g. Course with code #1. The Themes should not provide duplicate records, like if you select all Themes where course_code is not 1 or null, in that case it will show the Themes linked to other Courses, even if it's already linked to the Course that I want.

Thank you in advance.

Upvotes: 0

Views: 43

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Actually, exists logic works well in this case:

SELECT
     t.theme_code,
     t.name
FROM themes t
WHERE NOT EXISTS (SELECT 1 FROM course_themes ct
                  WHERE t.theme_code = ct.theme_code AND ct.course_code = 1);

If you wanted to use a join approach, you could try a left anti-join:

SELECT DISTINCT
     t.theme_code,
     t.name
FROM themes t
LEFT JOIN course_themes ct
    ON t.theme_code = ct.theme_code AND ct.course_code = 1
WHERE
    ct.theme_code IS NULL;

Upvotes: 1

Related Questions