Do not want to display record with same ID

[beemomo]

Hi everyone, i have a database with the information like:

id transId transType
1001 3001 1
1002 3002 1
1003 3002 0
1004 3005 1

1) 1001, 1002, 1003, 1004 is primary key

2) 3001, 3002 , 3003 is the order's invoice no.

3) 1,0 under trans type is their type: 1, is sales, 0 is cancel order

i wish to get this information from database, to display them at report
but i only want to display 1001, and 1004, meanwhile 1002, and 1003 to be excluded since thier transId is same (a customer make an order, and cancel by tat day so i don't want to display "zero result" record)


Can anyone help me to use sql to get a list from database ???which i don't want those 2 records to be displayed?

Many thanks...

[Delerna]

Does this help, it covers the scenario you present
[code=sql]
SELECT Min(ID) as ID,TransID,max( TransType) as TransType
FROM YourTable
GROUP BY TRANSID
HAVING count(ID)=1
[/code]

[beemomo]

Dear Delerna,

Wow , the codes works exactly the way i want. However, i am confuse that why shoud we select the Min(ID) ? Can i have your further explanations?

Thank you very much

[Delerna]

Dear Delerna,
why shoud we select the Min(ID) ? Can i have your further explanations?

Sure
This is an agregate query and as such every field in the selection must be agregated in some way, otherwise you get an error.
So min is just a way for me to include the ID in the selection without getting that error.
For this particular field in this particular query i could just as well have used max or first or last in order to provide the aggregate function necessary in order to include it in the selection list.

I hope that makes sense

[beemomo]

Sure
This is an agregate query and as such every field in the selection must be agregated in some way, otherwise you get an error.
So min is just a way for me to include the ID in the selection without getting that error.
For this particular field in this particular query i could just as well have used max or first or last in order to provide the aggregate function necessary in order to include it in the selection list.

I hope that makes sense

Oops, that's the way it works.. Many thanks again! :p