CODEWITHSUNDEEP
mysqlsqldatabase
shane
shane

Reputation: 1863

select from database table rows that are not in another table

I have a table of 'entries' in a MYSQL database. I have another table that records activity on those entries, with the id of the entry as a foreign key. I want to select from my first table entries that do not appear in the second table.

How can I use SQL to make this happen? Do I have to iterate through both tables and compare every entry with every other entry? Is there an easier way to do this?

ex. I have a table with an entry data column and a user name column. I have another table with an entry id column and a user id column. I want to select from my first table all of the entries which do not appear in the second table with a given user id.

Thanks ahead of time. I have been struggling with this experiment for a while. I imagine I have to join the two tables somehow?

Upvotes: 0

Views: 94

Answers (2)

Neghtasro
Neghtasro

Reputation: 221

From what I understand, you want to select all rows where the foreign key doesn't match anything in the other table. This should do the trick:

SELECT *
FROM Data A
RIGHT JOIN Entry B
ON A.ID = B.ID
WHERE A.ID IS NULL

Here's a handy chart that illustrates how to use joins for stuff like this.

You can also use NOT IN, and the mechanics for this one are actually a bit easier to understand.

SELECT *
FROM Data A
WHERE A.ID NOT IN (SELECT ID FROM Entry)

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

Several ways to achieve this, NOT IN, NOT EXISTS, LEFT JOIN / NULL check. Here's one with NOT EXISTS:

SELECT *
FROM FirstTable T
WHERE NOT EXISTS (
    SELECT *
    FROM SecondTable T2 
    WHERE T.Id = T2.Id
    )

Upvotes: 3

Related Questions