Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Subquery nightmare...sorry this is a long one!

Author  Topic  J K Starting Member 7 Posts Posted - 2001-11-08 : 04:37:50 Hi there, There is something I'm trying to do, which is going to need a pretty complex query and I have found a couple of ways of doing this which don't quite work...I wondered if you could help? I have a database that has news articles in one table (tblNewsandAppeals), and another lookup table of categories (tblCategory) that can be associated with that article. The third table is a transactional child table that puts these two together - so item 1 can have a category of 3, and then item 1 could also have another record in there that has a category of 5, so Item 1 has categories 3 and 5. If you get my meaning. This leads to records in this table (tblCategory_Item) in which the item id appears many times, but the unique ID of the record makes them different. I think this is where the solution lies somewhere... The problem is this... I need to extract list of distinct items which have NOT got category 41 associated with them anywhere (as things with this category are pulled up somewhere else on the page, and I do not want to duplicate content). I tried this first of all:- "SELECT News_ID, News_type, Area, Division,Title, Expires, Status, Submitted,Par1 FROM dbo.tblNewsandAppeals a WHERE EXISTS (SELECT * FROM dbo.tblNewsandAppeals b INNER JOIN dbo.tblCategory_Item ON b.News_ID = dbo.tblCategory_Item.Item_ID WHERE Status = 1 AND Expires >= " + Replace(todaysdate, "'", "''") + " AND dbo.tblCategory_Item.Category_ID <> 41 AND dbo.tblCategory_Item.Table_name = 'tblNewsandAppeals' AND Division = " & division & " AND News_type = 3 AND a.News_ID = b.News_ID) ORDER BY Submitted DESC" ...but it still will return news items that may have category 41 associated with them. e.g., say news item 1 has categories 3 and 41 associated with it, it excludes the instance of the news item that has category 41, but still pulls up the same news item with association to category 3 (the other conditions in the where clause shouldn't affect the problem). So news item 1 turns up in my recordset, and it shouldn't because it was associated with category 41. How do I get around this? I think I need some sort of nested subquery to root out all those news items that have category 41 first, and then get rid of any duplicate news_IDs with the EXISTS clause above... This is really getting frustrating! I am now doing this by having 3 seperate views which run off eachother in my SQL Server DB - one which selects the correct news, then one that gets rid of all News_IDs that have 41 associated with them, then one that removes duplicates from that list - but this is much much slower, and rather more inelegant! There has to be a much better way with one single query statement!!! Hope you can help, look forward to hearing from you! Please save me from going insane!! Regards, Julia Krajewska