Table 1 record should not be present in Table 2

I have two tables in the same database in mysql:

1. Table 1
2. Table 2

I am using phpmyadmin and php.

I have the column ‘teamid’ in both the tables and table 1 contains several different events columns. So I have pulled out teamid who are participating in particular events from table1.

I also have column teamid in table2 who have participated in particular events. So I have also pulled out teamid who are participating in particular events from table2.

Query 1: Select teamid From table1 where event1pay=’Paid’
Query 2: Select teamid From table2 where event=’event1′

So from Query 1 and Query 2, I pulled out teamid.

Table1: it has 55 records

Table2: it has 5 records.

I want table1 teamid records should not be present in table2 teamid records.

The required query should return 50 records.

I have applied Left Join, NOT IN but it is not working as both the above queries have different where clause.

Answer

I would recommend not exists:

select *
from table1 t1
where  event1pay = 'Paid' and not exists (
    select 1 
    from table2 t2 
    where t2.event = 'event1' and t2.teamid = t1.teamid
)

If you want to do this with an anti-left join, that would be:

select t1.*
from table1 t1
left join table2 t2 on t2.teamid = t1.teamid and t2.event = 'event1'
where t1.event1pay = 'Paid' and t2.teamid is null

But I find that not exists is a better way to express what you want to do here.