Simple Transaction search to find entries with account on any line

I have been struggling with this, basically the need is pretty simple, i want to search for all transactions in which a particular account (lets say 001) is used on any line.

e.g.

there are 100 transactions with 500 account lines in a search
i want to get the list of 2 transactions containing all 5 account lines which has account 001 in any line atleast once.

so my result set would be something like this

doc num     account     amount
abc             001            10.00
abc             009            20.00
abc             122            30.00
xyz              002            15.00
xyz              001             25.00

Note : that each trasaction contains one line of 001 atleast (can be more but 001 should be present in the transaction)

this is doable in sql by using window functions and partition by clause but it gives mostly unexpected error in NetSuite.