CODEWITHSUNDEEP
sqlsqlitecompare
Babbara
Babbara

Reputation: 488

Compare two values with other two specific values in SQL

I have two table structured as well:

Teacher | Day | Month               Day | Month
  Red     12    June                12    June
 Blue     19    May                 8     September 
 Green    15    July                2     May

I would like to exclude all the dates that appear in the second table, so I thought to structure the query with an AND in the where clause structured as well: AND (day NOT IN ("+day.table+") AND month NOT IN ("+month.table+"))" but on this way it removes all the data with one of the day or one of the month that there are in the second table (it only gives me the teacher Green). How can I improve it? PS: month.table and day.table are simply two SELECT where I get all the days the all the months

Upvotes: 0

Views: 54

Answers (2)

Jithin Joy
Jithin Joy

Reputation: 136

You can use LEFT JOIN and eliminate the entries where there's an entry in the second table.

SELECT * FROM table1 AS t1 LEFT JOIN table2 AS t2 
ON t1.Day = t2.Day AND t1.Month = t2.Month 
WHERE t2.Day IS NOT NULL;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Are you looking for not exists?

select t1.*
from t1
where not exists (select 1 from t2 where t2.day = t1.day and t2.month = t1.month);

Upvotes: 1

Related Questions