I am trying to SELECT records from two MySql tables. I would like all records from the first table excluding specific records, by date, from the second table. For example:
Table1 T1id
, firstName
, LastName
Table2 id
, T1id
, hours
, rate
, date
T1id
is the link between the two tables, therefore when the tables are joined I would have T1id
, firstName
, lastName
, hours
, rate
, date
Let's say there is a record in Table2 with a date
of 2012-02-08. With one query, I need to select all records from Table1, excluding the record from Table2 that has the date of 2012-02-08.
I've tried a few variations of JOINS and UNIONS, however I either get all records, a bunch of duplicate records, or one record (i.e. Table2 date). I apologize, but I do not have a specific piece of code to include since nothing has worked for me.
So many times this is typically done with a NOT EXISTS subquery, but subqueries can be big performance hits in larger tables... However, by doing a LEFT JOIN and looking for NULL is in essence, the same result
select
t1.*
from
table1 t1
left join table2 t2
on t1.t2.t1id
AND t2.date = '2012-02-08'
where
t2.t1id IS NULL