SQL logic problem

[daseparo]

Hi,

My problem is more about sql logic and working with result of query. I am new in using this report engine so i still don't know many advantages. Please help me.

First, i want to say that my english writing is bad, so forgive me about mistakes made in this text.

 

Problem:

It is about stock trading in one firm. I have 2 tables in Mysql base. One is all peoples and companies who bought stocks of that firm. In the second table I have transaction history. Id of seller an buyer(from first table) and how many stocks (amount) were included in that transaction(3 fields). Other fields are not necessary at this moment.

 

I make report where you choose one person, and you can see history of all transaction order by date and name of the second side in that transaction. I use:

SELECT * FROM tabletrans , tablepersons WHERE (tabletrans.IDBuyer=? AND tablepersons.ID=tabletrans.IDSeller) OR

(tabletrans.IDSeller=? AND tablepersons.ID=tabletrans.IDBuyer)

 

But my problem is that in that report you cannot easy see what transaction was. Did you bought or sold stock. I'm interested if there is way that i inspect my results and depend on where is id of person (you want to see history for, if is in the seller or buyer id column) add aditional field where i explain nature of transaction for that person, or just make amount negative or positive depend on seling or buying.

 

I kind solve this problem with two kind of report, but my boss doesn't like them. :-). He wants the way i explain before. First report i make is with 2 lists. First when you(means person you choose) buying and second is when you selling. And second report is where i make list off all transactions and put seller and buyer id in that list, second side name,surname and id. I also put in report title person name and id. So when you reading report you have to see what is person id, and in the list of transaction find it in idbuyer or idseller column.