Hi all,

I am trying to weight results of various subqueries and amalgamate
them into a single result set.

I can't rely on UNION to remove duplicate rows since I am manually
attributing different scores to each subquery, so they are not
duplicate. Instead I am having to add an additional NOT IN clause
which lists the results of the subqueries already executed.

This seems very ineffecient to me, and just wondered if there was any
way of telling MySql to just reuse the results of the subequery that's
already executed. I've tried assigning an alias to each subquery but
it tells me my query syntax is wrong.

Example of the kind of thing I want to do:

SELECT (SELECT *, 100 as score FROM table WHERE [complexsubquery1] AS
sub1)
UNION ALL
SELECT (SELECT *, 50 as score FROM table WHERE [complexsubquery2] AND
id NOT IN (sub1.id) AS sub2 )
UNION ALL
SELECT (SELECT *, somecol * 2 as score FROM table WHERE
[complexsubquery3] AND id NOT IN (sub1.id UNION sub2.id)
ORDER BY score

Can anyone tell me if this is possible and I just have the syntax
wrong, or is there some other way to accomplish what I want?

I'm using MySQL 5.0 if that makes any difference.

Many thanks for any

Lister

On 12 Oct, 10:13, lister <listerofsme...@hotmail.com> wrote:
> Hi all,
>
> I am trying to weight results of various subqueries and amalgamate
> them into a single result set.
>
> I can't rely on UNION to remove duplicate rows since I am manually
> attributing different scores to each subquery, so they are not
> duplicate. Instead I am having to add an additional NOT IN clause
> which lists the results of the subqueries already executed.
>
> This seems very ineffecient to me, and just wondered if there was any
> way of telling MySql to just reuse the results of the subequery that's
> already executed. I've tried assigning an alias to each subquery but
> it tells me my query syntax is wrong.
>
> Example of the kind of thing I want to do:
>
> SELECT (SELECT *, 100 as score FROM table WHERE [complexsubquery1] AS
> sub1)
> UNION ALL
> SELECT (SELECT *, 50 as score FROM table WHERE [complexsubquery2] AND
> id NOT IN (sub1.id) AS sub2 )
> UNION ALL
> SELECT (SELECT *, somecol * 2 as score FROM table WHERE
> [complexsubquery3] AND id NOT IN (sub1.id UNION sub2.id)
> ORDER BY score
>
> Can anyone tell me if this is possible and I just have the syntax
> wrong, or is there some other way to accomplish what I want?
>
> I'm using MySQL 5.0 if that makes any difference.
>
> Many thanks for any
>
> Lister

You could create temoporary tables.