Re: Multiple tests against the same subquery.

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Eric (ejproductions-msgbrd6321_at_mailblocks.com)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 08:23:37 -0700

Woo hoo! As soon as I read what you posted I slapped my forehead. I *knew*
it had to be possible. I changed what you did just a little (because the
change would work for what I was doing and might be a little more
efficient), but you put the magic in the join clause - and that was the key.
I couldn't quite figure out how to go about doing that, and now I feel
stupid for not seeing it in the first place. If your curious, here's what I
wound up doing:

SELECT

    DISTINCT
    mt.ID

FROM

    MyTable mt
    INNER JOIN (subquery) sq
    ON
    (
        mt.Column1 = sq.ID OR
        mt.Column2 = sq.ID OR
        mt.Column3 = sq.ID OR
        ....[etc]
    )

I'm only selecting the ID column because this whole query is actually a
subquery in yet another query. It works perfectly, I am extremely grateful
for your expertise!

Eric

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:dj6n70l2qec2cvliqkl8egdbnnav1fo523@4ax.com...
> On Mon, 12 Apr 2004 17:48:52 -0700, Eric wrote:
>
> >Hi All,
> >
> >I have a need for a query where the WHERE clause checks multiple fields
> >against a subquery. The following example returns the results I want:
> >
> >SELECT
> > *
> >FROM
> > MyTable
> >WHERE
> > Column1 IN (subquery) OR
> > Column2 IN (subquery) OR
> > Column3 IN (subquery)
> >
> >In reality, I actually have eight separate tests (rather than three, as
in
> >the example) and "subquery" is a very complicated query (it is the same
> >every time, however). I would really, really like to be able to do this
in
> >one statement, but I would like to avoid running the same (very
complicated)
> >subquery eight separate times. Because of the nature of the subquery,
> >placing it in a view is not an option (I'm trying to keep this question
> >simple so I won't waste your time with an explanation as to why using a
view
> >won't work).
> >
> >Here's my actual question... in "pseudo SQL", I need something like this:
> >
> >SELECT
> > *
> >FROM
> > MyTable
> >WHERE
> > ((Column1 OR Column2 OR Column3) IN (subquery))
> >
> >If any of you know of a way of doing this, I would be extremely happy!
If
> >not, I suppose I'll have to put the whole thing in a sproc where I would
do
> >the subquery first, place the results in a temp table, and reference the
> >temp table in my WHERE clause. However, this is a last resort beause I'd
> >really like to do this in one statement.
> >
> >Thank you for your help!
> >
> >Eric
> >
>
> Eric,
>
> Try it with:
>
> SELECT
> DISTINCT MyTable.*
> FROM
> MyTable
> INNER JOIN
> (SELECT (...) AS ResultColumn
> FROM (rest of subquery) ...) AS sq
> ON
> sq.ResultColumn IN (Column1, Column2, Column3)
>
> Note: don't forget to add MyTable in the select clause, or else you'll
> get the ResultColumn from the subquery as well. Also, don't forget the
> DISTINCT, to prevent getting the same row from MyTable twice if the
> result from the subquery matches agains two of the three columns.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Two Queries
    ... you can't use a subquery that reguires square braces embedded in the subquery. ... Since your field and table names meet the requirements you can simply paste the subquery into the query. ... tblEmployees LEFT JOIN Query13 ... ... FROM tblJobNames INNER JOIN ((tblEmployees ...
    (microsoft.public.access.queries)
  • Re: Two Queries
    ... since the only value you really need from the subquery is ... FROM tblJobNames INNER JOIN ((tblEmployees ... FROM tblEmployees INNER JOIN tblChecks ... Plus this query is probably much more efficient than a Not Exists ...
    (microsoft.public.access.queries)
  • Re: How to alias a Join->Correlated Subquery
    ... I want a query to list: ... The date and name of the first programme submission planned by ... Fails with the error "You have written a subquery that can return more than ... Isn't that exactly what the "TOP 1" clause means? ...
    (microsoft.public.access.queries)
  • Re: access compact database changes results
    ... Is there any chance that the last recordyou expect in the subquery have ... reliably (compact, add more records, run query - get wrong results etc.). ... The Top 32 clause does indeed return variable numbers of records as it ...
    (microsoft.public.access.queries)
  • Re: MAX
    ... Stacked = Subquery in From Clause ... "John Spencer" wrote: ... since it does reference the main query. ...
    (microsoft.public.access.queries)