RE: Subquery?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Clifford,

Thanks for this. When I was trying to get to an answer, I think I had
subquery in my head and forgot about the self-join. Much appreciated.

Regards,

k

"Clifford Bass" wrote:

Hi Kohai,

Try this, which avoids the sub queries and grouping:

select distinct A.*
from (trades as A inner join trades as B on B.tName = A.tName) inner join
trades as C on C.tName = A.tName
where B.tTrans = "buy" and C.tTrans = "sell";

Clifford Bass

"kohai" wrote:

I have a table that has the following fields:
tDate, tName, tTrans, tAmt, tPrc

I am trying to write a query for all tDates where I can get all of the data
for any of the tNames where tTrans has both Buys and Sells. There are a lot
of names that are only on one side or the other, but I can't figure out to
get a query that returns only those that had both types of transactions.

Thanks for your assistance.

Regaards,
Kohai
.

Relevant Pages

  • RE: Subquery?
    ... tDate, tName, tTrans, tAmt, tPrc ... I am trying to write a query for all tDates where I can get all of the data ... for any of the tNames where tTrans has both Buys and Sells. ...
    (microsoft.public.access.queries)
  • Subquery?
    ... tDate, tName, tTrans, tAmt, tPrc ... I am trying to write a query for all tDates where I can get all of the data ... for any of the tNames where tTrans has both Buys and Sells. ...
    (microsoft.public.access.queries)
  • RE: Subquery?
    ... from (trades as A inner join trades as B on B.tName = A.tName) inner join ... I am trying to write a query for all tDates where I can get all of the data ... for any of the tNames where tTrans has both Buys and Sells. ...
    (microsoft.public.access.queries)
  • Re: Date Comparison
    ... query - any type of query. ... existing records for completed trades (i.e. there is BOTH a start and ... There will always be some live trades. ...
    (microsoft.public.access.queries)
  • Unique Index resulting in full table scan
    ... Trades: A wide table with about 1.8 million rows. ... CurveRef: Used to link a trade to curves. ... Has 3 columns: TradeId, ... When I write a query like ...
    (comp.databases.sybase)