Select within select?

[udigold1]

Hi,



I'm trying to get a query that returns buy and sell orders that were made at the same time and at the same price (to see if someone is trying to 'fix' the price).
I've got two tables - one table is named 'Trades' and holds trades information (ticker, price, time, account number, etc.), the other table, 'accounts', holds account information (account number, account name, account manger, etc)
My first query returned all buy and sell trades that were made at the same time:
Original Query:

SELECT DISTINCT trades.*
FROM trades INNER JOIN trades AS Tmp ON trades.Ticker = Tmp.Ticker WHERE (((trades.Ticker)=Tmp.Ticker) And ((trades.DateOf)=Tmp.DateOf)
 And ((trades.timeOf)=tmp.timeOf) And ((trades.Quantity)<0) And ((Tmp.Quantity)>0) And And ((trades.Price)=Tmp.Price) And ((accounts.codeHafrada)=1))
 Or (((trades.Ticker)=Tmp.Ticker) And ((trades.DateOf)=Tmp.DateOf) And ((trades.timeOf)=[tmp.timeOf]) And ((trades.Kamut)>0) And 
((Tmp.Kamut)<0 And ((trades.Price)=Tmp. Price))
ORDER BY trades.Ticker, trades.DateOf, trades.timeOf;

Result:

Ticker	Time	Quantity	Price	CodeOf	Account Number
Bla	10:20AM	-50	120	1	12
Bla	10:20AM	70	120	0	15
Bla	11:30AM	200	140	1	24
Bla	11:30AM	-180	140	1	44

However, I want to narrow it down so my query will return buy and sell orders just from specified accounts (where the field codeof=1)

Modified Query:

SELECT DISTINCT trades.*
FROM accounts INNER JOIN (trades INNER JOIN trades AS Tmp ON trades.Ticker = Tmp.Ticker) ON accounts.AccountNum = trades. AccountNum
WHERE (((trades.Ticker)=Tmp.Ticker) And ((trades.DateOf)=Tmp.DateOf) And ((trades.timeOf)=tmp.timeOf) And ((trades.Kamut)<0) And ((Tmp.Kamut)>0)
 And ((trades.TimeOfriska)=Tmp.TimeOfriska) And ((accounts.codeOf)=1)) Or (((trades.Ticker)=Tmp.Ticker) And ((trades.DateOf)=Tmp.DateOf) And
 ((trades.timeOf)=[tmp.timeOf]) And ((trades.Quantity)>0) And ((Tmp.Quantity)<0) And ((trades.Price)=Tmp.Price) And ((accounts.codeOf)=1))
ORDER BY trades.Ticker, trades.DateOf, trades.timeOf;

The problem with the modified query is that although it omits trades with accounts , it leaves the other side of the trade (the sell in this example):

Ticker	Time	Quantity	Price	CodeOf	Account Number
Bla	10:20AM	-50	120	1	12
Bla	11:30AM	200	140	1	24
Bla	11:30AM	-180	140	1	44

But I want it to return just buy and sell where both accounts have 'codeof=1'.... like this:

Ticker	Time	Quantity	Price	CodeOf	Account Number
Bla	11:30AM	200	140	1	24
Bla	11:30AM	-180	140	1	44

So I need to somehow to select first just trades with accounts that have codeof=1 and then select the trades that have the same time and price....

But how do I do that?

Thanks in advance

[rpeare]

The easy way to do this is (assuming you know the account code(s) you are looking for:

1. Create a query based on your tables that filters for the specific account(s)
2. Run your 'same time' query based on the QUERY you just made rather than the original table.

I'm pretty sure this could be done in a single query if you want to provide something with some junk data in it (I'm lazy and do not want to re-create your table structure with data to work it out)