Subquery?

[kohai]

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

 

[kohai]

I came up with:

SELECT *
FROM trades as m
WHERE (((m.tname) In (select tname from trades as t1 where (t1.trans =
"buy") group by t1.tname having count(*) > 1) And (m.tname) In (select tname
from trades as t2 where (t2.trancode = "sell") group by t2.name having
count(*) > 1)));

I'm sure there is a more eloquent way, but it appears to work.

 

[Clifford Bass]

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]

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]

Hi Kohai,

You are welcome! A different solution would be to use a couple of "not
exists" subqueries, which was my first thought.

Clifford Bass