mysql - SQL: Selecting/Ignoring records on the basis of tables in an array -

given following schema:

create table test     (`id` int, `testname` varchar(7), `description` varchar(55));  insert test     (`id`, `testname`, `description`) values     (1, 'test1', 'td1'),(2, 'test2', 'td2'),(3, 'test3', 'td2');  create table forcedata     (`id` int, `forcedata` varchar(7), `description` varchar(55));  insert forcedata     (`id`, `forcedata`, `description`) values     (1, 'f1', 'fd1'), (2, 'f2', 'fd2'), (3, 'f3', 'fd3');  create table temp     (`id` int, `tempname` varchar(7), `description` varchar(55));  insert temp     (`id`, `tempname`, `description`) values     (1, 'tm1', 'tmd1'),(2, 'tm2', 'tmd2');      create table sensor     (`id` int, `sensorname` varchar(7), `description` varchar(55));  insert sensor     (`id`, `sensorname`, `description`) values     (1, 's1', 'sd1'),(2, 's2', 'sd2'); 

i want write select statement gets value tables mentioned in 1 array , data should not in table of other array. want this:

declare @listtocheck varchar(max) set @listtocheck = 'test' + ', ' + 'forcedata';  declare @tablestoavoid varchar(max) set @tabletoavoid = 'temp' + ', ' + 'sensor';   select id tables in ('+@listtocheck+')' , id should not in ('+@tablestoavoid+')' 

so above schema record id:3 should selected. in advance :)

try this,

mysql:

   select t.id     (         select id         test          union          select id         forcedata         ) t     left join (         select id         temp          union          select id         sensor         ) t1 on t.id = t1.id     t1.id null 

sql server:

select id test  union  select id forcedata  except  (     select id     temp      union      select id     sensor     )