Question when subtracting two queries

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.Problem resolved. Thinking was off.

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.

|||

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.

TRY below query and check if that works...

select * from
(
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c on t.object_id = c.object_id
) x
left outer join
(
--213
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t on s.major_id = t.object_id
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
) y on x.[DataBase Name] = y.[DataBase Name]
where y.[DataBase Name] is null