SQL query question #2

[bob laughland]

Hi All,

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

I have a table like this

rowid name changed
1 a 1
1 b 0
1 c 1
1 d 0
2 ....
2 ...
2 ....

I want to be able to find the next rowid in my table which matches my
criteria (my table has thousands, even hundreds of thousands of rows)

The criteria is the trouble you see. I have the notion when querying
against the name field of 'required' values and 'optional' values. So
for example if I said give me the first rowid which has a required
value 'a' and an optional value 'z' it would still give me the first
rowid (1) because z was an optional value.

But if I turned it around and said the required value was 'z' and
optional was 'a' it would not return rowid 1 because the first set of
rowids there do not have a value z.

That first requirement can be achieved with a query like this,

select * from table where rowid in
(select top (1) RowId from table where Name in ('required1',
'required2')
group by rowid having Count(Rowid) >= 2)
and Name in ('required1', 'required2','optional1','optional2')

So the inner query there forces the number of rows to be above 2 there
which would correspond to a match of 2 required values. The outer
query then returns back the rest of the rows with the matching rowid
including the required and optional values.

That works fine, but there is one more requirement which I have not
been able to handle. See the changed column there in the table above.
The next criteria is that at least one of the optional or required
values much have a value of 1 in the changed column. I came up with a
query to do this before, but it required another subquery which was
too slow.

Please help, even if you are not sure please help. I need to nail this
one.

[Hugo Kornelis]

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

[bob laughland]

On Oct 30, 11:58*am, Hugo Kornelis
<[e-mail]:

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.

[Hugo Kornelis]

On Wed, 29 Oct 2008 21:29:09 -0700 (PDT), bob laughland wrote:

>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.

Hi Bob,

I think I can help you write your query. I might also be able to find
the root cause of the performance problem and a possible fix. But to do
that, I do need the information I already mentioned in my previous post:

* CREATE TABLE statements for all tables involved. Please include all
constraints, indexes, and properties - though you may omit columns that
are completely irrelevant to the problem.

* INSERT statements with some well-chosen rows of sample data. Make sure
that they work against the CREATE TABLE statements, and avoid using
non-portable date formats such as DD-MM-YYYY or MM-DD-YYYY.

* The results expected from the sample data chosen. Try to ensure that
all relevant cases should be included in the sample data, so that the
expected results are a good test case for the query functioning as
required.

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