select count(value) from (select value from genenametype where type = 'ordered locus' and value ~ '(CP|SF?)[0-9][0-9][0-9][0-9](\.[0-9])?' union select extra as value from (select propertytype.value as extra from propertytype inner join dbreferencetype on propertytype.dbreferencetype_property_hjid = dbreferencetype.hjid where dbreferencetype.type = 'EnsemblBacteria' and dbreferencetype.id ~ 'AAN[0-9][0-9][0-9][0-9][0-9]' and propertytype.type = 'gene ID' and propertytype.value ~ 'SF[0-9][0-9][0-9][0-9]') as f left join (select value from genenametype where type = 'ordered locus' and value ~ '(CP|SF?)[0-9][0-9][0-9][0-9](\.[0-9])?') as g on f.extra = g.value where g.value is null) as combined; | select count(value) from (select value from genenametype where type = 'ordered locus' and value ~ '(CP|SF?)[0-9][0-9][0-9][0-9](\.[0-9])?' union select extra as value from (select propertytype.value as extra from propertytype inner join dbreferencetype on propertytype.dbreferencetype_property_hjid = dbreferencetype.hjid where dbreferencetype.type = 'EnsemblBacteria' and dbreferencetype.id ~ 'AAN[0-9][0-9][0-9][0-9][0-9]' and propertytype.type = 'gene ID' and propertytype.value ~ 'SF[0-9][0-9][0-9][0-9]') as f left join (select value from genenametype where type = 'ordered locus' and value ~ '(CP|SF?)[0-9][0-9][0-9][0-9](\.[0-9])?') as g on f.extra = g.value where g.value is null) as combined; |