pokerplayer23 pokerplayer23 - 2 years ago 80
SQL Question

SQL Server - Return all records from LEFT table and only non matching records from right table

I have 2 tables with the same structure (field names). Table1 and Table2.

I need to return all records from Table1 and only records from Table2 that do not match/join to a record in Table1.

Table2 has more records than Table1.

I am joining the 2 tables on 3 fields.

So basically I want all records from table1 returned and only records that don't have a match (joining on the 3 fields) to table1 from table2 returned.

Put another way, Table1 records take precedence over table2 records in my final result output when the records exist in both tables (same value for the 3 fields)

I started writing something like the below but I don't think it will work. Should I use a left outer join instead?

Select * from table1 t1
left join table2 t2 on = and = and t1.custid= t2.custid
where is null or is null or t2.custid is null

Answer Source

So, you need every row from table1 plus the rows from table2 that don't match with table1?:

FROM table1
FROM table2 t2
                 WHERE id =
                 AND date =
                 AND custid = t2.custid);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download