Ken Tanzer wrote > So I frequently have to provide information like "what were all the types > of services this client received during the last quarter?" or "show me all > the clients who received service X last year." I've learned enough to use > ANY, array_agg and unnest to get through these queries, but if I'm going > about this wrong or there's a better way to do it I'd love to know about > it! Your example query does not ask those questions. SELECT DISTINCT service_code FROM (SELECT unnest(services_rendered_array) AS service_code FROM services_tables WHERE ...) svcs; SELECT DISTINCT client_id FROM ( SELECT * FROM services_table WHERE 'X' = ANY(services_rendered_array) ) svcs; In neither case do you need to use a sub-query answer the question. Namely, what you describe makes use of arrays only, and not relations (though the allowed array item values could be defined on a table somewhere). Option A: A. T1: session_id, client_id, service_codes[], date Note that A is the basic structured assumed for the two example queries above. Option B: B. T1: session_id, session_date, client_id B. T2: session_id (FK-many), service_code B. T2 would have a single record for each service performed within a given session while A. T1 models the multiple service aspect of a session by using an array. Incorrect Option C: C. T1: session_id, session_date, client_id C. T2: session_id, service_codes[] This makes use of a one-to-many relationship but also embeds yet another "many" aspect within C. T2 This is generally going to be a bad idea as you are now mixing the models together. And note that I do qualify this as generally since you may very well decide that C is an elegant and/or the most correct way to model your domain. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771359.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
