How can I get PK from this query?

[tpizarro93]

I have this query:

SELECT {p.productId} FROM {PriceRow as p} GROUP BY {p:productId}, {p:store}, {p:price} HAVING count(*) > 1

But I don't need the productId, I need the PK. How can I get that? I tried to changed {p.productId} for {p.pk} but it doesn't works.

[former_member634058 · Accepted solution]

Hi,

If you are using productId instead of association of products in PriceRowModel, then you can try using the following query:

SELECT {prod.pk} from {product as prod} where {prod.code} IN ({{SELECT {p.productId} FROM {PriceRow as p} GROUP BY {p:productId}, {p:store}, {p:price} HAVING count(*) > 1}}

However, note that this will be a slow running query because of IN clause. Please use wisely.

Thanks.

[tpizarro93 · Accepted solution]

Thanks! It works