How to clean up this simple query?

[Riley DeWiley]

I am continually amazed by SQL's ability to humble me ....

I have a toy query into a toy database that looks just like this:

SELECT [f].[f]
FROM f, fw, w
WHERE f.id = fw.fid and fw.wid = w.id and w.id in (((((select distinct w.id
from w where w.x in ('a', 'b') )))))
GROUP BY [f].[f]
HAVING count(f.id) = (select count(id) from (((((select distinct w.id from w
where w.x in ('a', 'b') ))))));
The query works and I am basically happy with it as it is, except it
contains the redundant string:

((((select distinct w.id from w where w.x in ('a', 'b'))))

duplicated precisely (I have marked it out with a few extra parens for easy
reading).
I need to do this query at runtime and would prefer not to do it twice ...
it just seems sloppy.

How can I eliminate the redundancy, perhaps by performing this query once
and storing the value to use a second time? Or does the optimizer do this
for me?

IOW, how can I do something like X = ((((select distinct w.id from w where
w.x in ('a', 'b')))), and then use X in place of the second evaluation of
this expression?

Thanks much.

Prefer reply to NG as my usual GMAIL account is flakey right now.

Thank you

RDeW

[Steve Jorgensen]

You basically have 2 choices with SQL. You either duplciate the expression in
the query or you perform a query of a query to eliminate the duplication. In
either case, you're hoping the optimizer can do a good job with what you give
it, and the only way to find out for sure is to try it and/or look at a query
execution plan generated by the SQL processor.

On Sun, 28 Nov 2004 22:14:30 -0800, "Riley DeWiley" <ri***********@gmail.com>
wrote:

I am continually amazed by SQL's ability to humble me ....

I have a toy query into a toy database that looks just like this:

SELECT [f].[f]
FROM f, fw, w
WHERE f.id = fw.fid and fw.wid = w.id and w.id in (((((select distinct w.id
from w where w.x in ('a', 'b') )))))
GROUP BY [f].[f]
HAVING count(f.id) = (select count(id) from (((((select distinct w.id from w
where w.x in ('a', 'b') ))))));
The query works and I am basically happy with it as it is, except it
contains the redundant string:

((((select distinct w.id from w where w.x in ('a', 'b'))))

duplicated precisely (I have marked it out with a few extra parens for easy
reading).
I need to do this query at runtime and would prefer not to do it twice ...
it just seems sloppy.

How can I eliminate the redundancy, perhaps by performing this query once
and storing the value to use a second time? Or does the optimizer do this
for me?

IOW, how can I do something like X = ((((select distinct w.id from w where
w.x in ('a', 'b')))), and then use X in place of the second evaluation of
this expression?

Thanks much.

Prefer reply to NG as my usual GMAIL account is flakey right now.

Thank you

RDeW

[Jarl Hermansson]

"Riley DeWiley" <ri***********@gmail.com> wrote in
news:10*************@corp.supernews.com:

I am continually amazed by SQL's ability to humble me ....

I have a toy query into a toy database that looks just like this:

SELECT [f].[f]
FROM f, fw, w
WHERE f.id = fw.fid and fw.wid = w.id and w.id in (((((select distinct
w.id from w where w.x in ('a', 'b') )))))
GROUP BY [f].[f]
HAVING count(f.id) = (select count(id) from (((((select distinct w.id
from w where w.x in ('a', 'b') ))))));
The query works and I am basically happy with it as it is, except it
contains the redundant string:

((((select distinct w.id from w where w.x in ('a', 'b'))))

duplicated precisely (I have marked it out with a few extra parens for
easy reading).
I need to do this query at runtime and would prefer not to do it twice
... it just seems sloppy.

How can I eliminate the redundancy, perhaps by performing this query
once and storing the value to use a second time? Or does the optimizer
do this for me?

IOW, how can I do something like X = ((((select distinct w.id from w
where w.x in ('a', 'b')))), and then use X in place of the second
evaluation of this expression?

Thanks much.

Prefer reply to NG as my usual GMAIL account is flakey right now.

Thank you

RDeW

I can't think of any way to get rid of that "reduntant string". If the
optimizer can re-use that duplicate part is depending on what database
product you are using... (Don't expect too much.)

But there are some things you can do to simplify your query, and probably
improve performance a little.

Take a look at the first, "main" WHERE-clause:
WHERE f.id = fw.fid
and fw.wid = w.id
and w.id in (select distinct w.id
from w
where w.x in ('a', 'b'))

DISTINCT doesn't make any sense here. Remove it and you'll have
WHERE f.id = fw.fid
and fw.wid = w.id
and w.id in (select w.id
from w
where w.x in ('a', 'b'))
Then take a closer look at the HAVING-clause:
HAVING count(f.id) = (select count(id)
from (select distinct w.id
from w
where w.x in ('a', 'b')))

This can easily be rewritten as:
HAVING count(f.id) = (select count(distinct id)
from w
where w.x in ('a', 'b'))
Now your query is as:
SELECT [f].[f]
FROM f, fw, w
WHERE f.id = fw.fid
and fw.wid = w.id
and w.id in (select w.id
from w
where w.x in ('a', 'b'))
GROUP BY "f"."f"
HAVING count(f.id) = (select count(distinct id)
from w
where w.x in ('a', 'b'))
If you know some details about the data, like unique constraints, you can
make the query even better. For example if the table w's id column is
unique, you can rewrite the query as:
SELECT [f].[f]
FROM f, fw, w
WHERE f.id = fw.fid
and fw.wid = w.id
and w.x in ('a', 'b'))
GROUP BY "f"."f"
HAVING count(f.id) = (select count(distinct id)
from w
where w.x in ('a', 'b'))
HTH,
Jarl

[Bas Cost Budde]

Riley DeWiley wrote:

I am continually amazed by SQL's ability to humble me ....

I have a toy query into a toy database that looks just like this:

SELECT [f].[f]
FROM f, fw, w
WHERE f.id = fw.fid and fw.wid = w.id and w.id in (((((select distinct w.id
from w where w.x in ('a', 'b') )))))
GROUP BY [f].[f]
HAVING count(f.id) = (select count(id) from (((((select distinct w.id from w
where w.x in ('a', 'b') ))))));
The query works and I am basically happy with it as it is, except it
contains the redundant string:

My guess is that it is just Access that fouls up the representation of
the query. The optimizer in Jet will not evaluate this twice, I think.
You could try to store it as a separate query, but I can't tell if that
will expedite or hamper the query engine.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[--CELKO--]

1) Please post DDL, so that people do not have to guess what the keys,
constraints, declarative referential integrity, datatypes, etc. In
your schema are. Sample data is also a good idea, along with clear
specifications.

2) Never name a column and a table the same thing. It is legal but
confusing and shows you have no data model -- a table is a set of
things and a column is a scalar, so you have confused a forest and a
tree.

3) Use and only one name for a column. You seem to be suffering from
the "magical, universal id" sdyndrome where the data element inherits
a table name when it is used somewhere else. Please read any book on
data modeling or ISO-11179 so you'll stop destroying your data
dictionary.

4) Why do think that excess parentheses will make the code easier to
read and maintain? It is not true. Why did you use needless
proprietary square brackets on names?

5) I am going to make a guess that the assorted "id" columns were
supposed to be named for a particular data element instead of a vague
universal attribute. I am going to use silly, but readable names for
tables. First hack:

SELECT Foobar.f
FROM Foobar AS F,
FooWoop AS FW,
Woop AS W
WHERE F.fid = FW.fid
AND FW.wid = W.wid
AND W.wid IN (SELECT W.wid
FROM Woop
WHERE x IN ('a', 'b'))
GROUP BY Foobar.f
HAVING COUNT(F.fid)
= (SELECT COUNT(DISTINCT W.wid)
FROM Woop
WHERE x IN ('a', 'b'));

This is gets rid of some of the query nesting. But since we have no
specs and no DDL, who knows??

How can I eliminate the redundancy, ... <<

1) Put it in a WITH clause, if you have an SQL that has implemented
that feature.

2) Let the optimizer handle it

3) Flatten out the query and get rid of the subqueries:

SELECT Foobar.f
FROM Foobar AS F,
FooWoop AS FW,
Woop AS W
WHERE F.fid = FW.fid
AND FW.wid = W.wid
AND W.x IN ('a', 'b')
GROUP BY Foobar.f
HAVING COUNT(F.fid)
= (SELECT COUNT(DISTINCT W.wid)
FROM Woop
WHERE x IN ('a', 'b'));

I am bothered by a COUNT(DISTINCT) on an idenrtifier column since it
shoudl be redundant. but without DDL, you knows??

[AK]

you haven't spe4cified RDBMS / version.
In general, google for "subquery factoring clause" / "WITH clause"

[pradeep]

Just try the below query

select f.f
from f, fw, (select distinct w.id from w where w.x in ('a','b')) MyW
where f.id = fw.fid and
fw.wid = MyW.id
group by f.f
having count(f.id) = (select count(id) from MyW);
I haven't tested it.

[Riley DeWiley]

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:18*************************@posting.google.co m...

<deletia>

Whatever. I am still thrashing over here. What I am looking for is an
implementation of the hangar problem in which the hangar and pilot tables
are joined by a junction table. I am building my hangar-analogue table on
the fly through a query into another table, all this in a db where the
"hangar" table is joined N:N with the "pilot" table through a bridge.

Make any sense? I think I can figure it out in about another three days or
so.

RDeW