<p>Since you mention &quot;policy&quot; it means you are only interested in multi asset transactions which are all contained in the <code>ma_tx_out</code> table.</p>
<p>However, you need to be aware that multiple assets can have the same policy. If this is what you want, the query would be:</p>
<pre><code>select ma_tx_out.* from ma_tx_out inner join multi_asset
  on ma_tx_out.ident = multi_asset.id
  where multi_asset.policy = '\x....' ;
</code></pre>
<p>If instead you want all transaction for the same multi-asset then the query would be:</p>
<pre><code>select * from ma_tx_out.ident = ? ;
</code></pre>

CC BY-SA 4.0
2022-08-08T03:16:58.887Z
Thank you Erik! 
So ma_tx_out are the tx_out records for any policy and  multi_asset.id is the id of an asset that belongs to that policy?
RelevantData
CC BY-SA 4.0
2022-08-08T04:04:44.757Z
Yes, all multi asset transactions are recorder in the `ma_tx_out` table. A `multi_asset.id` is actually a tuple of `(policy, name)` so that `(policy1, name1)` is different from `(policy1, name2)`. I suspect that you are actually interested in the `multi_asset.id` rather than the `policy`.
Erik de Castro Lopo
CC BY-SA 4.0
2022-08-08T04:06:12.510Z
If you do `select * from multi_asset ;` you will see that each `multi_asset` row has a `fingerprint` which is probably what you should be using rather than `policy`.
Erik de Castro Lopo
CC BY-SA 4.0
2022-08-08T04:09:48.363Z
If you do `select * from multi_asset order by policy limit 10 ;` you will see that there are rows with the same `policy` field, but since they have different `name` fields, their `fingerprint` fields also differ.
Erik de Castro Lopo
CC BY-SA 4.0
2022-08-08T04:38:33.340Z
...how can I use: 

where multi_asset.policy = '\x....' ;

...if the 'policy' column contains a byte array? Explicit conversion to a literal in the sql query itself? 
Thanks very much for all your responses Erik! I am learning!
RelevantData
CC BY-SA 4.0
2022-08-08T04:54:16.867Z
ohh haha
https://www.postgresql.org/docs/current/datatype-binary.html
RelevantData
CC BY-SA 4.0
2022-08-08T02:31:31.240Z
2022-08-08T02:31:31.240Z
Erik de Castro Lopo
<p>What is the best way (or perhaps only way) to query db-sync for transactions involving a specific policy?</p>

<cardano-node><cardano-db-sync>
How to efficiently query for all transactions involving a particular policy
# How to efficiently query for all transactions involving a particular policy

(Asked by: username_0 on 2022-08-07)

What is the best way (or perhaps only way) to query db-sync for transactions involving a specific policy?

**Tags:** cardano-node, cardano-db-sync

---

## 1 answers

### Answer by username_1 on 2022-08-08. Score: 1 | Accepted answer

Since you mention "policy" it means you are only interested in multi asset transactions which are all contained in the ma_tx_out table.
However, you need to be aware that multiple assets can have the same policy. If this is what you want, the query would be:
select ma_tx_out.* from ma_tx_out inner join multi_asset
  on ma_tx_out.ident = multi_asset.id
  where multi_asset.policy = '\x....' ;

If instead you want all transaction for the same multi-asset then the query would be:
select * from ma_tx_out.ident = ? ;

> **Comments:**
> **username_0**: Thank you Erik!  So ma_tx_out are the tx_out records for any policy and  multi_asset.id is the id of an asset that belongs to that policy?  
> **username_1**: Yes, all multi asset transactions are recorder in the `ma_tx_out` table. A `multi_asset.id` is actually a tuple of `(policy, name)` so that `(policy1, name1)` is different from `(policy1, name2)`. I suspect that you are actually interested in the `multi_asset.id` rather than the `policy`.  
> **username_1**: If you do `select * from multi_asset ;` you will see that each `multi_asset` row has a `fingerprint` which is probably what you should be using rather than `policy`.  
> **username_1**: If you do `select * from multi_asset order by policy limit 10 ;` you will see that there are rows with the same `policy` field, but since they have different `name` fields, their `fingerprint` fields also differ.  
> **username_0**: ...how can I use:   where multi_asset.policy = '\x....' ;  ...if the 'policy' column contains a byte array? Explicit conversion to a literal in the sql query itself?  Thanks very much for all your responses Erik! I am learning!  
> **username_0**: ohh haha https://www.postgresql.org/docs/current/datatype-binary.html  

---

