sql - SELECT remaining rows for each parameter -

i have sql server database, of need select remaining rows 2 views.

the thought behind this: store game divisions in 1 table, , clans in another.

• the vwgetgamedivisions gets games can subjected division.
• the vwgetclandivisions gets current game divisions clan subscribed to.

currently, using

select dbo.vwgetgamedivisions.name, dbo.vwgetclandivisions.clanname   dbo.vwgetclandivisions  right outer join        dbo.vwgetgamedivisions     on dbo.vwgetclandivisions.gamename = dbo.vwgetgamedivisions.name 

gets 'divisions', , 'clans' registered them.

i want display remaining divisions (the rest clan isn't registered to), can bind dropdownlist clan see can still subscribe to.

i still quite newbee on sql queries, , don't know how go this..

i've tried where (dbo.vwgetclandivisions.clanname = null), returns divisions have no clan @ all.

edit - structures:

create table [dbo].[tblsiteclangamedivision](     [id] [int] identity(1,1) not null,     [clanid] [int] not null,     [gameid] [int] not null,     [removed] [tinyint] not null,     [dateadded] [datetime] not null,  constraint [pk_tblsiteclangamedivision] primary key clustered   create table [dbo].[tblsitegame](     [id] [int] identity(1,1) not null,     [name] [nvarchar](200) not null,     [description] [nvarchar](700) null,     [thumbnaillocation] [nvarchar](100) null,     [genreid] [int] not null,     [isdivision] [tinyint] not null,  constraint [pk__tblgame__3213e83f03317e3d] primary key clustered  

---

create view [dbo].[vwgetclandivisions] select dbo.tblsiteclandetail.clanid, dbo.tblsiteclandetail.clanname, dbo.tblsitegame.id gameid, dbo.tblsitegame.name gamename,                           dbo.tblsiteclangamedivision.removed, dbo.tblsiteclangamedivision.dateadded   dbo.tblsiteclandetail  inner join  dbo.tblsiteclangamedivision on dbo.tblsiteclandetail.id = dbo.tblsiteclangamedivision.clanid  inner join dbo.tblsitegame on dbo.tblsiteclangamedivision.gameid = dbo.tblsitegame.id  go  create view [dbo].[vwgetgamedivisions] select  id, name, thumbnaillocation, isdivision    dbo.tblsitegame   (isdivision = 1)  go 

the key use inner join fetch divisions clan signed to, , left join fetch ones aren't. left join return divisions if clan isn't signed them, , filter out ones are, so:

-- matching divisions select gd.id, gd.name, cd.clanname dbo.vwgetclandivisions cd inner join dbo.vwgetgamedivisions gd on cd.gameid = gd.id  -- remaining divisions select gd.id, gd.name, cd.clanname dbo.vwgetclandivisions cd left join dbo.vwgetgamedivisions gd on cd.gameid = gd.id gd.gamename null 

notice second query uses left join rows on both sides of join, , where clause shows rows there no matching game division clan. can use table name aliases (as above) shorten queries.

edit: should have been using right outer join, in testing hasn't worked. method given @vincent james of using not in (with caveat have specify clan searching by, otherwise list of empty divisions). here basic sql example:

create table #a (id int, bid int) create table #b (id int)  insert #b (id) select 1 insert #b (id) select 2 insert #b (id) select 3 insert #b (id) select 4 insert #b (id) select 5  insert #a (id, bid) select 1,1 insert #a (id, bid) select 1,2 insert #a (id, bid) select 1,3 insert #a (id, bid) select 2,1 insert #a (id, bid) select 2,2 insert #a (id, bid) select 3,3 insert #a (id, bid) select 3,4  select distinct #b.id  #b  id not in(select bid #a id = 1)  drop table #a drop table #b 

according this, entries #a id of 1 linked #b id of 1, 2 or 3 (not 4 or 5). running above query shows not in method returns 4 , 5, correct. i'm not entirely sure why right outer join isn't working, purpose of return every row #b regardless of if found in #a.

edit 2: removed right outer join method had 2 conflicting where clauses prevent rows being returned. isn't possible using outer join given table structure, because need select id on left, left side null. not in correct method, shown above.

edit 3 here stored procedure should need create. better hard coding query protects sql injection attacks.

create procedure [dbo].[getemptyclandivisions] (     @clanid int ) begin     -- clan name     declare @clanname nvarchar(255)     set @clanname = select top 1 clanname tblsiteclandetail clanid = @clanid       -- list of divisions clan registered     select distinct id, name, @clanname      vwgetgamedivisions     id not in(         select gameid          tblsiteclangamedivision          clanid = @clanid     ) end