I have two tables in a database (Animal1 and Animal2). In animal1 there is a field called Animal_ID (1,2,3,4,5). In Animal2 there is also a field called Animal_ID (2,3,4).
I want to: Show only the Animal_ID of Animal1 which will exclude the numbers from Animal2 Example Output must be: 1,5 becuase 2,3,4 is in both tables.
Thank you
create a TQuery with the SQL :
SELECT * FROM Animal1 WHERE Animal1.ID NOT IN ( SELECT ID from Animal2)
Where do you want to show them?
If you want to loop throu the Query use
with Unit.TQuery do begin
First;
while not EOF do begin
// do your thing
Next;
end;
end;
You can also use the exists clausule, which is much faster than "not in". Put the code above to Your Query1.SQL property.
select animal1.animal_id
from animal1
where not exists(select animal2.animal_id from animal2 where animal2.animal_id = animal1.animal_id)
Another option, (which I think is the best from performance point of view) to use a LEFT JOIN like so:
SELECT Animal1.Animal_ID
FROM Animal1 LEFT JOIN Animal2 ON Animal1.Animal_ID = Animal2.Animal_ID
WHERE Animal2.Animal_ID IS NULL
For the best performance make sure Animal_ID are Primary Keys or Indexes (where duplicates are allowed)
NOTE: I assume you know about TQuery/TDataSet etc. (You did not specify that information, and also which DBMS you use...)
