Skip to main content
A
ACS LLC
Inspiring
November 7, 2011
Question

Multiple "WHERE" statements in SQL depending on number of records retrieved

  • November 7, 2011
  • 2 replies
  • 2677 views

I guess this can be done in a stored Proc, but anybody any idea if and how to create a cfquery that has multiple WHERE statements based on the number of records?

Right now I have a query that runs, and gets 4 random records, based on criteria "A" defined in the WHERE statement, now if it only gets 1 record, I hit the dbase again, retrieving the same columns, but with a different clause in the WHERE, and so on, 4 times until I end up with my set of records.

It would be better if I had a little logic in the SQL so that it would say, find 4 random records, and if there are only 1, then run with this WHERE and if I still don't have a total of 4 (or whatever I set it to), then use this where and so on and so on

Btw I am using MS SQL2008

Thanks

Mark

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
November 8, 2011

Regarding, "It would be better if I had a little logic in the SQL so that it would say, find 4 random records, and if there are only 1, then run with this WHERE and if I still don't have a total of 4 (or whatever I set it to), then use this where and so on and so on"

in theory, yes.  In practice, not necessarily.  On more than one occasion I have found that mulitple trips to the db followed by Q of Q was faster than a single trip to the db.  These are exceptions to the rule though.

A
ACS LLCAuthor
Inspiring
November 8, 2011

It's not a huge table, and it's all indexed, but each query does require a few queries within the query and that happens up to a max of 4 times, the benefit is that I could also write this one query to a session immediately so that it's available on other pages, so if the page gets kicked back I don't have to re-run the query, with my query over query stuff etc... .but the vast majority of pages probably won't get kicked back so it might be better to have something a little slower on the rare occassion to keep the vast majority of pages running faster. Until I get it into full production it would be difficult to tell if the 4 queries is a faster approach.

I'm not sure about Owains solution CASE and WHEN are alien to me right now

-
-__cfSearching__-
Inspiring
November 8, 2011

I'm not sure about Owains solution CASE and WHEN are alien to me right now

 

It is just like cfwitch/cfcase.  You know, with all the database work you are doing lately, I am surprised you have not taken a good sql course to get more familiar with this stuff. Hint, hint ...

Owainnorth
Owainnorth
Inspiring
November 8, 2011

You'll have to use a subquery of some kind, as obviously a query cannot know how many rows it's returning until all the WHERE clauses have been applied

As it's SQL 2008, you may well be able to do something clever with functions that return tables, there are a few ways of doing it now. I'm thinking you might be able to do some kind of:

SELECT something,

CASE MyCustomFunctionWhichGetsRows()

  WHEN 1 THEN

   WHEN 2 THEN

  WHEN 3 THEN

   WHEN 4 THEN

END CASE

FROM table

But to be honest you'll probably just be best off using a proc.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices