Query to report what numbers in a range do not exist.

[fraxx]

I have a database with storage system configuration.
Table "Array" contains names of storage subsystems.
Table "Host" contains hostnames in each storage subsystems.
Table "LUN" contains LUN numbers used by each host.

Host references Array, and LUN references Host. This seems to work as expected.

I have made a query to generate the LUN numbers in use per host per array as such:


SELECT
array.objectname AS "Array"
,cast (host.hostname as char(18)) AS "Hostname"
,lun.lunnumber
FROM LUN, Host, Array
WHERE rHost = Host.objectid AND Host.rArray = Array.uid

ORDER BY array.objectname,host.hostname,lun.lunnumber ASC

This produces a report on what lun numbers are in use.

host.objectid is the primary key in host, and array.uid is the primary key in array.

Now I need to report on what lun numbers are NOT used per host per storage system. LUN numbers range from 1 to 255.

I have created a temp table with all possible lun numbers:
declare @counter as int
set @counter = 1
create table #lunids (
lunid int
)
while (@counter < 256)
begin
insert into #lunids
values
(@counter)
set @counter = @counter + 1
end


I'm looking for ideas on how to compare the previous result to the temp table and report only the free ie not existing lun numbers.

I've been trying with some joins and lun.number != #lunids.lunid but I feel I'm missing something essential.

 

[SQLSister]

First you need to stop using the comma syntax for joins. This is part of why you can't solve this. This is a simple problem using outer joins but if you are using that syntax, you can't use an outer join in that syntax and get correct results (as it often interprets that as a cross join). So convert to ansi standard joins and never, ever consider using the comma join syntax again.

basic syntax to solve a problem like this is:

select a.* from
table1 a 
left join 
table2 b on a.id = b.fkid
where b.pkid is null

That would give you the records in a that are not in b.

"NOTHING is more important in a database than integrity." ESquared