Re: SQL query question #2

On Oct 30, 11:58 am, Hugo Kornelis
<h...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
On Wed, 29 Oct 2008 15:33:07 -0700 (PDT), bob laughland wrote:
Hi All,

I have another SQL question, and this one is going to be difficult to
explain.

(snip)

Hi Bob,

First - in the future, please post CREATE TABLE and INSERT statements,
as knowing the table structure makes it easier to understand the problem
and having test data makes it easier to test. And I don't have the time
to manually create a table or enter test data, so you (who needs the
question answered) are the one who should invest time to spoonfeed me
what I need to help you. :)

Second - based on your description, I fail to see where the optional
values weigh in. If a rowid has all the required values, then you want
to return it whether the optional values are or are not present. So I'd
say ditch the optional values and loook at the required values only.

Third - reread the reply by Joe Celko to your first question. He
describes two methods of relational division, one with nested subqueries
and another one with GROUP BY and HAVING. The extra requirement
introduced here is easy to implement if you use this second version, as
all it takes is one extra line in the HAVING clause:

 AND SUM(CASE WHEN changed = 1 THEN 1 ELSE 0 END) >= 1

If changed is some numeric type, you could even simplify this to
 AND SUM(changed) >= 1

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Thanks for that. Sorry to be a bit vague. I am under pressure to get
this problem solved. For the moment I am also looking into a totally
different solution.

But yeah - back to your response. The optional values do come into it,
but only because of the requirement to take note of the changed value.

E.g. if I query the above data with required values of 'b' and 'd'.
Neither 'b' or 'd' has a changed value of 1, so it should move onto
the next row (2) etc.

But what if I query on required values 'b' and 'd' and optional value
of 'c' then I want to return that row back as valid because the
optional value of 'c' does have a changed value of 1.

You also gave the idea of using this,

AND SUM(CASE WHEN changed = 1 THEN 1 ELSE 0 END) >= 1

I did have that the other day, but that changes the query from taking
4 seconds to run to a minute. It is very expensive doing it like that.

Thanks.
.

Relevant Pages

  • Re: Help for a simple (?) query - part 2
    ... "I'm stuck with a simple query. ... ORDER BY LastUsedDate DESC) ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.mseq)
  • Re: How To Return A "Range Of Rows"??
    ... Shouldn't I be seeing results with the column headers? ... this just to show that the query works in debug mode? ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)
  • Re: reduce time for search query
    ... execute. ... The query fetches records which will have to sorted by ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)
  • Re: Weird speed problem
    ... query first and the first query last, or if you run both queries ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ... Both query plans use indexes on for both the tables though. ...
    (comp.databases.ms-sqlserver)
  • Re: Newbie: Help writing a sproc
    ... using the test data you posted earlie (changing the time on one row ... If you're sure you didn't make a mistake in copying the query, ... query will set the userid to NULL if the userid has at least one entry in ... So if userid 3 has an entry of 70 ...
    (microsoft.public.sqlserver.programming)