-3

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

1
  • Hi, and welcome to dba.stackexchange.com - please take a look at minimal reproducible example for ways to construct your question that help provide actionable details.
    – Hannah Vernon
    Jun 28 at 18:57
0

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;

Not the answer you're looking for? Browse other questions tagged or ask your own question.