0

How to fetch a list of distinct IDs using one query and then feed this list as an input parameter for IN() or ANY() of another query in Postgres.

(SELECT DISTINCT id FROM inventory WHERE supplier_id = 37) AS idlist  
(SELECT SUM(item_price) FROM transaction WHERE person_id IN (idlist)) AS output

NOTE: Specifically, I don't want to use subquery as a constraint in WHERE clause as proposed in some of the answers below. And I don't want to use nested subqueries.

Jimski
  • 826
  • 8
  • 23

1 Answers1

0

You can just use your SELECT DISTINCT .... query as a sub-query within the second query's IN() criteria.

That is:

SELECT SUM(item_price) 
FROM transaction 
WHERE person_id 
IN 
(
    SELECT DISTINCT id 
    FROM inventory 
    WHERE supplier_id = 37
)

And, in this sort of instance, you don't specifically need the DISTINCT keyword in that sub-query

Craig
  • 1,123
  • 3
  • 13
  • This is not the answer to my question. My question specifically asked for using the result of a query in IN or ANY of another subquery, and not for using subquery as a constraint in WHERE clause. – Jimski Apr 20 '23 at 05:27
  • Hey @Jimski - your question doesn't say anything in particular about "ANY". I will admit, the way the question is set out simply looks to me like someone who didn't understand the use of "IN" in SQL syntax – Craig Apr 20 '23 at 06:00
  • This old-ish question is probably still relevant, in terms of describing the usage of both IN and ANY with a sub-query (https://stackoverflow.com/questions/3699356/difference-between-in-and-any-operators-in-sql) .... essentially, the same principal still applies. You may need to elaborate a bit more on your particular use-case / business-case if you feel these answers are not satisfying your requirement – Craig Apr 20 '23 at 06:03
  • The old-ish question is not relevant. My question is not about the difference between IN and ANY but about passing a result form subquery into IN or ANY of another subquery. – Jimski Apr 20 '23 at 19:48
  • 1
    I guess you could use string_agg() to build an array string that could be passed to ANY(), but if you're specifying individual values to IN() it doesn't work in quite the same - you can't just pass a single string of comma-delimited values to IN(). To be honest, without still understanding the specific use-case of what you're doing, it's hard to understand exactly why using the initial query as a sub-query is not an option. Anyhow, either way, I think doing a string_agg() and passing that to ANY() is your best option. – Craig Apr 20 '23 at 22:14
  • Why do you say that I can't pass a comma delimited string to IN()??? I'm doing it all the time when string is specified explicitly and it works just fine. – Jimski Apr 24 '23 at 05:06
  • Hey @Jimski. I'll admit I'm not as familiar with postgresql as other RDBMS - are you saying that postgresql will return results (where an "id" value is either a, b or c) if have SELECT * from table WHERE id IN ('a,b,c') ... rather than IN ('a','b','c')? If that is actually the case then string_agg() will be your likely solution in both cases. (As a comparison, if the query in SQL Server was ... IN ('a,b,c'), then the "id" value would have to actually be "a,b,c" (as opposed to one of either a, b or c). Does that all make sense? – Craig Apr 25 '23 at 22:55