use of NOT IN in select statement 1

[Richey]

Hi

I've got 2 tables Categories and tblID

Categories contains 4 columns (ID, BroadItem, Item, RelatedItem)

Sample Data
-----------
142, Forestry, tree maintenance, N/A
143, Forestry, logging, N/A
144, Forestry, consulting, N/A
145, Forestry, Timber Mgt, N/A

the user can select one or more of these categories which values get stored in tblID (ID, BroadID, uniqueid, Detailsd)

Sample Data
-----------
tree maintenance, forestry, 81, 2504
logging, forestry, 81, 2504
tree maintenance, forestry, AC, 2505
logging, forestry, AC, 2505
Timber Mgt, forestry, AC, 2505

the user can be uniquely identified by columns uniqueid or detailsid.
if user 2505 comes back and want to add a new category it should only give them consulting as a choice (because they hacve already selected the other 3)
User 2504 should get 2 choices, consulting and Timber Mgt.

I tried creating the following view and using in my asp as

sqlstmt="Select * FROM vwCatDetails where Expr1 = '" & request("UserID") & "'"

which i thought was working ! but using that user 2504, for example, they would only receive one choice, namely consulting, because user 2505 has already selected three (if that makes sense) I want user 2505 to get 1 choice and user 2504 to get 2 choices !

hope that makes sense !?!
thanks

CREATE VIEW dbo.vwCatDetails
AS
SELECT DISTINCT TOP 100 PERCENT a.*, b.DetailsID AS Expr1
FROM         dbo.Categories a INNER JOIN
                      dbo.tblID b ON a.BroadItem = b.BroadID
WHERE     (a.Item NOT IN
                          (SELECT     id
                            FROM          tblID
                            WHERE      broadid = a.broaditem))
ORDER BY 3 asc

 

[amrita418]

I think you need one more condition in the inner select stmt where clause.

and DetailsId = b.DetailsID

Regards,
AA

 

[Richey]

thanks aa

that still only returns one record for user 2504 instead of two.

 

[amrita418]

You did not post the final code but here is what I tried and it worked.

create table #Categories  (ID int, BroadItem varchar(30), Item varchar(50), RelatedItem varchar(10))

Sample Data
-----------
insert into #Categories values(142, 'Forestry', 'tree maintenance', NULL)
insert into #Categories values(143, 'Forestry', 'logging', NULL)
insert into #Categories values(144, 'Forestry', 'consulting', NULL)
insert into #Categories values(145, 'Forestry', 'Timber Mgt', NULL)

select * from #Categories
create table  #tbiD (ID varchar(50), BroadID varchar(30), uniqueid char(2), Detailsd int)

select * from #tbid

Sample Data
-----------
insert into #TbiD values('tree maintenance', 'forestry', '81', 2504)
insert into #TbiD values('logging', 'forestry', '81', 2504)
insert into #TbiD values('tree maintenance', 'forestry', 'AC', 2505)
insert into #TbiD values('logging', 'forestry', 'AC', 2505)
insert into #TbiD values('Timber Mgt', 'forestry', 'AC', 2505)


SELECT     distinct TOP 100 PERCENT 
           a.*, 
           b.DetailsD AS Expr1
FROM       #Categories a 
           INNER JOIN #tbiD b 
           ON a.BroadItem = b.BroadID
WHERE     a.Item NOT IN (SELECT     id
                         FROM       #tbiD
                         WHERE      broadid = a.broaditem
                                    and b.Detailsd = DetailsD)
ORDER BY 3 asc

Regards,
AA

 

[Richey]

thanks

I haven't got access to my sql server at the moment, but i will give it another try and let you know - thanks again.

 

[Richey]

thanks aa !

that does seem ok now - I'll lwet you know any further problems - thanks for your help and sorry for the delay in replying....................

 

[Richey]

hi aa

sorry to bother you but its not quite right. because there are alot of different categories if you insert a few extra rows into categories like.........

insert into #Categories values(146, 'Business Services', Auctioneering', NULL)
insert into #Categories values(147, 'Business Services', Security services', NULL)
insert into #Categories values(148, 'Education', 'Private schools', NULL)

say the user decides they are suddenly related to Education as well as forestey and select forestry and education! then it works ok for forestry but no choices (just Private Schools in this example) will show for education ?


sorry !

 

[Richey]

has anybody got any ideas about this ?
I'm wondering if its a union that is needed in there somewhere ?

thanks

 

[amrita418]

I am not sure I follow your requirement correctly. Can you show some sample data and expected output.

If you are wondering why the 3 new rows added did not show up in the output then the reason is you have inner join between the two tables.

Regards,
AA

 

[Richey]

thanks aa

it was the join - I simply removed it !

thanks again