Creating a "virtual" table... 2

[MCAR]

Hi, I want to make a query to obtain a complementar result, let me try to simplify the question, considering the table:

NAME  NUMBER
----  ------
a     5
a     8
a     9
b     2
b     3

I'd like to receive the numbers that doesn't appear for a name, with number < 10, for instance. If I had a table with all the numbers in a column, I could build the query, but I think that I can solve this in a simple way. Obs: the database is Oracle.

 

[r937]

personally, i love the integer table, and have no qualms about generating one in any database i'm working on as soon as the need comes up

after all, it comes in handy in all sorts of queries, and once you've declared the integers table, you can just use it

if necessary, when you get a problem that requires very many integers, if you're not sure how many you have, you can just SELECT MAX(i) FROM INTEGERS to see if you need to add more

create table integers
( i integer primary key );
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
etc.

now this table can easily be brought into use in many different situations

for one example, see

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid494805_tax285649,00.html

in your case, numbers that don't have a name, i.e. numbers that aren't in your table, you need an outer join --

select NUMBER
from integers
left outer
join yourtable
on i=NUMBER
where i between 1 and 10
and NUMBER is null

i can explain that if you want me to

yes, you can also do it by generating the numbers &quot;on the fly&quot; as it were

i don't know about oracle, but in sql/server you can say

select 1
union
select 2
union
etc.
select 10

and then use this query as a derived table basically by just wrapping parentheses around it, giving it an alias, and then sticking it into the query above in place of the integers table

in oracle you could select from dual, which is only minimally more verbose

in other databases you'd have to invent a one-row table just like dual, so rather than select from it 10 times, you'd be better off to create the integers table and forget about that ugly UNION (imagine doing that for i=25, i=50,...)

which is why i like the integers table

rudy

 

[mwolf00]

I really like the idea of the integer table Rudy (you get a star). I haven't really thought about it, but I've made temp tables in stored procedures to do the same thing. Might as well keep a permanent one. I'd create it with a loop in a stored procedure...(T-SQL used)

CREATE PROC dv_makeInts

AS
SET NOCOUNT ON
DECLARE @i INT
CREATE TABLE integers(i INT)


SELECT @i = 1
WHILE @i <= 5000
BEGIN
	INSERT INTO integers VALUES (@i)
	SELECT @i = @i + 1
END
GO

EXEC dv_makeInts

DROP PROC dv_makeInts
GO

 

[MCAR]

Thanks people, we are using a query with an auxiliar &quot;integer&quot; table and the MINUS operator, based in rudy's sugestion.