I know I can use either a left or right outer join to find records that exist in 2 tables and the records that exist in 1 but not the other. So how would I exclude the records that exist in both tables to show only the records that exist in 1 table but not the other?
If you database supports subselects and the “IN” operator, you could try something like:
SELECT * FROM Table1 WHERE Id NOT IN (SELECT ID FROM Table2)
Might be slow depending on table size/indexes, but it is easy to write!
Subqueries can be slow, so the following may be faster:SELECT Table1.* FROM Table1
LEFT JOIN Table2 ON (Table1.ID = Table2.ID)
WHERE Table2.ID IS NULL
Al
Ah yes, that would be much better.
Tried the second statment and returned 0 rows, this is simialr to my current statement:
SELECT s.part as ‘NS Part’,
c.part_for as ‘Access Part’
from sales s left outer join catalog1 c on s.part = c.part
where (s.dates >= ‘{Root Container.Popup Calendar.text}’ and
s.dates <= ‘{Root Container.Popup Calendar 1.text}’)
order by s.part
When I add the c.part_for = Null condition to the where statement I return 0 rows.
You need to use “IS NULL” rather than “= NULL”, because NULL does not equal NULL.