June 8, 2006 at 9:05 am
| im tryin to get the numbers from 1 to 7 that have not been added to the table already this is my Simple query select Day from foodmapping Group by Day which returns 4 5 so i need 1 2 3 6 7 returned from this query any ideas wud be appreciated thanks in advance |
June 8, 2006 at 9:54 am
DECLARE @Numbers TABLE( [Number] integer)
INSERT INTO @Numbers
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7
DECLARE @FoodMapping TABLE( [Day] integer)
INSERT INTO @FoodMapping
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 7
SELECT [Number] FROM @Numbers
WHERE [Number] NOT IN( SELECT [Day] FROM @FoodMapping GROUP BY [Day])
I wasn't born stupid - I had to study.
June 8, 2006 at 10:27 am
perfect thanks a million man much appreciated
June 8, 2006 at 10:39 am
For those that love being complicated for the sake of it:
create table nos ( i int)
create table a (i int)
insert into nos select 4 union all
select 5
declare @i int
declare @max-2 int
set @max-2 = 7
select @i = 0
while @i < (@Max)
begin
set @i = @i + 1
if not exists (select * from nos where i = @i)
insert a select @i
end
select * from a
drop table nos
drop table a
EDIT: cant spell
June 8, 2006 at 11:42 am
That's an especially good solution if you are not starting from one and ending at a specific number.
The other side, (I have not seen Remi for a while) you should have a numbers tables in your db for this and many other purposes...
I wasn't born stupid - I had to study.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply