TSQL Query help - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

TSQL Query help

Author

Topic

CodeDreamer68
Starting Member

9 Posts

Posted - 2011-04-30 : 13:09:59

I need help with a query.

In simplest terms, I have three tables. Let's call them News, SearchRules and SourceFilters

Note: I'm leaving out columns that do not apply to this question

News has the following columns:
NewsID (int)
SourceID (int)
Title (nvarchar(256)

SearchRules has the following columns:
RuleID (int)
RuleName (nvarchar(256))

SourceFilter has the following columns:
RuleID (int)
SourceID (int)

I return all News records where the SourceID of the News record is also found in any SourceFilter record that has a RuleID of 210.

In other words, if the SearchRule selected by the user has Sources to filter by, then return only News that were from the SourceID's found in the SourceFilter table matching the SearchRule.RuleID selected.

Here's what I did to do that...

SELECT News.Title
FROM News
INNER JOIN SourceFilters
ON News.FeedID = SourceFilters.FeedID
WHERE SourceFilter.RuleID = 210

But here's the twist, if there are no rows in the SourceFilters table for RuleID 210, then I want to return all News records, without filtering by Source.

Anyone have any ideas of the best way to accomplish this?

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-30 : 14:12:33

In your query example you join by column feedID that is not in your tables structure. My guess you had in mind sourceID actually.

Anyway the solution is as simple as:

if exists(select *
            FROM SourceFilters 
            WHERE RuleID = 210
          )
   SELECT News.Title
     FROM News          n
     JOIN SourceFilters f ON n.sourceID = f.SourceID
     WHERE f.RuleID = 210
else
  select n.title from news n

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

CodeDreamer68
Starting Member

9 Posts

Posted - 2011-04-30 : 14:26:37

Thanks for your reply.

That's similar to the solution I just came up with...


SELECT Title
FROM   News
WHERE NOT EXISTS (SELECT NULL 
		  FROM SourceFilters
	          WHERE RuleID = @RuleID)
OR EXISTS (SELECT NULL
	   FROM SourceFilters
	   WHERE RuleID = @RuleID
	   AND News.FeedID = SourceFilters.FeedID)

But subquery solutions aren't efficient. I'd rather use joins, but I think at this point there just isn't any other way.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-30 : 14:34:18

You are welcome.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources