Help on query 1

[tirf]

create table table1
(site Numeric,
 site_network varchar(100)
 )
 
 insert into table1
 select 1, 2 UNION ALL
 select 1, 3 UNION ALL
 select 1, 4 UNION ALL
 select 2, 1 UNION ALL
 select 2, 4
 
 create table table2
(protocol Numeric,
 site Numeric,
 protocol_network varchar(100)
 )
 
 insert into table2
 select a, 1, 2 UNION ALL
 select a, 1, 5 UNION ALL
 select b. 2, 6

I have these 2 tables

table1 contains data of sites on certail networks and table2 contains protocol data on sites and networks

I want to get protocol data where any of protocol networks (protocol_network) don't match to the site's network (site_network) it is registered to

for the data I gave above I want to get the following resultset

protocol site protocol_network
b 2 6

because protocol b is registeted to protocol_network 6 and protocol_network 6 is not a Network the site (in this case site 2) registered to (site 2 is registeted to nerworks 1 and 4)

Thanks

 

[gmmastros]

This?

Select Table2.Protocol,
       Table2.Site
From   Table2
       Left Join Table1
         On Table2.Site = Table1.Site
         And Table2.protocol_network = Table1.site_network
Group By Table2.Protocol, Table2.Site
Having Count(Table1.site) = 0

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tirf]

that was helpful

Thanks George