Is there a way to identify those records not found within a where IN() statement?

Question

From PHP Code $Lines is defined as a list of accessions e.g. 123,146,165,1546,455,155

plant table has sequential records with the highest idPlant (unique identifier) of say 1000.

My simple SQL Query:

SELECT * FROM plant WHERE `plant`.idPlant IN($Lines) order by plant.idPlant;

This brings back row data for '123,146,165' etc.

Is there away to be told that '1546' was not found? (and thus the user probably entered a typo, I can not use a 'confirm all numbers are below X' because in the real data the idPlant may not be sequential and the upper bound will increase during use).

Update:

Looking to get an output that will tell me what Numbers were not found.

Answer 1

You can build up a sub query using unions that returns a list of all your values, then LEFT JOIN against that, checking for NULL in the WHERE clause to find the non matching values.

Basic php for this would be something like this:-

<?php

$sub_array = explode(',', $Lines);

$sub = '(SELECT '.implode(' AS i UNION SELECT ', $sub_array).' AS i) sub0';

$sql = "SELECT sub0.i 
    FROM $sub
    LEFT OUTER JOIN plant 
    ON  plant.idPlant = sub0.i 
    WHERE plant.idPlant IS NULL";

?>