CODEWITHSUNDEEP
mysqlleft-joinunions
user175328
user175328

Reputation: 323

SQL Query To Get All Records From One Table, Except A Specific Record, By Date, From Another Table

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.

Upvotes: 0

Views: 3116

Answers (2)

DRapp
DRapp

Reputation: 48139

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

Upvotes: 1

John Woo
John Woo

Reputation: 263703

USE INNER JOIN if you are sure that T1id exists in both tables:

SELECT  a.T1id, 
        a.FirstName, 
        a.LastName, 
        b.hours, 
        b.rate, 
        b.date
FROM table1 a INNER JOIN table2 b
        ON a.T1id = b.T1id
WHERE b.date <> DATE('2012-02-08')

but if you want to get all T1id from Table1 (which exists or does not exists in Table2) use LEFT JOIN

SELECT  a.T1id, 
        a.FirstName, 
        a.LastName, 
        b.hours, 
        b.rate, 
        b.date
FROM table1 a LEFT JOIN table2 b
        ON a.T1id = b.T1id
WHERE b.date <> DATE('2012-02-08')

Upvotes: 1

Related Questions