CODEWITHSUNDEEP
sql-server
MaiOM
MaiOM

Reputation: 936

SQL Server many-to-many query

I have two tables, let's call them table1 and table2. They both have a column called ID1 and ID2 which are respective PK for each of two tables.

I have also another table, called table3 which contains ID1 and ID2, establishing a many to many relation between these two tables.

What I need is to get all the records from table2 that are not related with records in table1.

Ex.

I need a query that will give me as result 2.

Can anyone suggest me a way to proceed?

Thanks

Upvotes: 0

Views: 330

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

SELECT t2.ID2
    FROM table2 t2
    WHERE NOT EXISTS(SELECT NULL
                         FROM table3 t3
                         WHERE t3.ID2 = t2.ID2);

You could also use a LEFT JOIN:

SELECT t2.ID2
    FROM table2 t2
        LEFT JOIN table3 t3
            ON t2.ID2 = t3.ID2
    WHERE t3.ID2 IS NULL;

Upvotes: 6

Related Questions