I have a database of information of a possible 1-100 rows. The issue is some of the numbers don't exist.
When I run a query the following results are retuned:
Unit,Location,Name,Description
1,1,a,xxxx
1,2,a,xxxx
1,3,a,xxxx
1,6,a,xxxx
1,7,a,xxxx
1,9,a,xxxx
1,10,a,xxxx
How can I get the following result instead:
Unit,Location,Name,Description
1,1,a,xxxx
1,2,a,xxxx
1,3,a,xxxx
1,4,-,----
1,5,-,----
1,6,a,xxxx
1,7,a,xxxx
1,8,-,----
1,9,a,xxxx
1,10,a,xxxx
You could use a table that would contains numbers from 1 to 100 and then use a left join in your query to get all numbers (from this table) join to the data in your other tables.
ex:
create table #data (id int, sometxt varchar(20));
create table #lookup (id int);
insert into #data values (1,'some stuff'), (2,'some other stuff'), (4, 'hey ! 3 is missing');
select * from #data;
insert into #lookup values (1),(2),(3),(4);
select l.id, d.sometxt
from #lookup l left join #data d on d.id=l.id;
drop table #data;
drop table #lookup;
