Re: Querry not given right results

From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 08/14/04 

Date: Fri, 13 Aug 2004 19:42:18 -0500

Dear Juan:

Not having a description of what you want this to accomplish, it's a
bit difficult. But I'm seeing something I find peculiar.

Let's concentrate on the subquery starting in line 4.

This introduces a new instance of the table TABLE1, and aliases it as
T. Then, within that subquery you reference the instance of TABLE1
from the outer query. I'm thinking you probably want to reference the
instance of TABLE1 in the inner query.

That's because the subquery usually needs to stand alone, except if it
needs to reference the correlated row in the outer query. With an IN
clause, you probably don't even want any reference outside the inner
query.

Have you written this inner subquery and made it work? If so, please
post it here.

Is it this:

SELECT [PART#]
  FROM TABLE1
  GROUP BY [PART#]
  HAVING Count([PART#]) >= 3
    AND STATUS2 In ("K","C","Z")
    AND SALES Like "B*"
    AND SATYPE = "BID"

This would indicate you want to see only those parts which have more
than 2 rows whose status2 is K, C, or Z, whose sales start with B, and
whose satype is "BID". Running this subquery separately should give
you this list. Does it?

When you transform this into a subquery for the IN clause, and you
alias TABLE1 in it to distinguish it from the instance of TABLE1 in
the outer query, you must change all the references to TABLE1 in this
inner subquery to be T thoughout the subquery. Making references to
the outer instance of TABLE1 which I see in the query you posted would
create a logic I cannot begin to digest. But I sure the computer
does, which would create results that would likely be unexpected.

I strongly recommend you alias both the table in the outer query and
the one in the subquery. Make them, for example, T1, and T2. That
way you cannot reference TABLE1 at any other place in the query. By
aliasing both of them, you must use an alias at every point in the
criteria, and you will probably reference them correctly. Not
aliasing one of them may leave some ambiguity in your mind.

Anyway, that's my theory about what I'm guessing you probably
intended. Using logic like that currently in your query is so
uncommon I can only guess it is not what you intended.

Any help here? I hope so! Please get back on what happens.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Fri, 13 Aug 2004 14:11:17 -0700, "juan" <Jhernan562@aol.com> wrote:

>Hello all,
>have following sql view query:
>
>SELECT TABLE1.[PART#], TABLE1.COST, TABLE1.QT_PRICE,
>TABLE1.ZPR1, TABLE1.ZPRC, TABLE1.ZQCM
>FROM TABLE1
>WHERE (((TABLE1.[PART#]) In (SELECT [PART#] FROM TABLE1 AS
>T
>GROUP BY [PART#]
>HAVING Count([PART#]) >= 3)) AND ((TABLE1.STATUS2) In
>("K","C","Z")) AND ((TABLE1.SALES) Like "B*") AND
>((TABLE1.SATYPE)="BID"))
>ORDER BY TABLE1.[PART#];
>But I'm not getting exactly what I want from the HAVING
>criteria. It seems that the query returns parts that match
>one or more of those criteria.Example,
>I get two parts
>1232
>1232
>But my count is >= 3, so I shouldnt' get these. But since
>these 2 parts have SATYPE (BID) I get them. So is there a
>way around this?
>Hope I'm clear. Please advise any input would really
>appreciate it.
>Thanks,
>Juan

Relevant Pages

  • Re: asterisk in select_list in queries...
    ... subquery or other subqueries that don't return data and aren't referenced by ... Listing only the required columns also increases the opportunities for SQL ... Server to optimize your query by making use of indexes. ... that needs to reference the new column. ...
    (microsoft.public.sqlserver.server)
  • Re: Querry not given right results
    ... the outer query, not on the inner query filtering PART#. ... >>Let's concentrate on the subquery starting in line 4. ... within that subquery you reference the ... >instance of TABLE1 ...
    (microsoft.public.access.queries)
  • Re: alias query
    ... If the form is open and displays the information, you can reference the ... FROM table1 AS t ... in this query AMonth and AYear are month and year of sample date. ...
    (microsoft.public.access.queries)
  • Re: Order by Alias Field Name?
    ... Or you could save the subquery as a separate query, and then join it to the ... Table1 table in a query; then you could sort on the field as desired. ... where table2.tbl1$id = T1.ID) as NumberOfChildRecords ...
    (microsoft.public.access.queries)
  • Re: Can some explain this to me?
    ... the subquery, but it can also reference any column in any table from ... resolved against the outer query table person. ... inside a transaction and if the count is different roll the ...
    (comp.databases.ms-sqlserver)
Quantcast