Thread: Search for lists
Hello, I need to search a table to find sets of rows that have a column matching itself for the whole set and another column matching row for row with a list I am going to supply. The result I should receive should be value of the column that matches itself. For example given the following data in my table: 3; 1 3; 2 4; 8 4; 9 4; 10 I might need to search for 1,2. This should produce the result 3. Or if I were to search for 8, 9, 10 the result should be 4. Searching for 8, 9 should produce an empty result as should 8, 9, 10, 11. Can anyone recommend a strategy?
Untested approach Use array_agg on column 2 along with group by on column 1 to build check arrays and then use equals to compare with an arrayof your desired input values. You should omit duplicates and order ascending both the data and the input to ensure you are matching canonical forms. David J. On Jun 18, 2011, at 17:51, Daron Ryan <daron.ryan@gmail.com> wrote: > Hello, > > I need to search a table to find sets of rows that have a column matching itself for the whole set and another column matchingrow for row with a list I am going to supply. The result I should receive should be value of the column that matchesitself. > > For example given the following data in my table: > > 3; 1 > 3; 2 > 4; 8 > 4; 9 > 4; 10 > > I might need to search for 1,2. This should produce the result 3. Or if I were to search for 8, 9, 10 the result shouldbe 4. Searching for 8, 9 should produce an empty result as should 8, 9, 10, 11. > > Can anyone recommend a strategy? > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
An alternative approach would be to select using a IN condition on the where clause and group by column 1 and column 2. Then, using this as a sub-select group by the resultant column 1 and a count on column two. The matching identifiers arethose with a count equal to the number of entries in the original IN condition. Basically count how many of values each distinct key in column 1 matches and keep those keys where the count and the numberof values match. David J. On Jun 18, 2011, at 17:51, Daron Ryan <daron.ryan@gmail.com> wrote: > Hello, > > I need to search a table to find sets of rows that have a column matching itself for the whole set and another column matchingrow for row with a list I am going to supply. The result I should receive should be value of the column that matchesitself. > > For example given the following data in my table: > > 3; 1 > 3; 2 > 4; 8 > 4; 9 > 4; 10 > > I might need to search for 1,2. This should produce the result 3. Or if I were to search for 8, 9, 10 the result shouldbe 4. Searching for 8, 9 should produce an empty result as should 8, 9, 10, 11. > > Can anyone recommend a strategy? > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Thanks. On 19/06/2011 8:09 AM, David Johnston wrote: > An alternative approach would be to select using a IN condition on the where clause and group by column 1 and column 2. Then, using this as a sub-select group by the resultant column 1 and a count on column two. The matching identifiersare those with a count equal to the number of entries in the original IN condition. > > Basically count how many of values each distinct key in column 1 matches and keep those keys where the count and the numberof values match. > > David J. > > > On Jun 18, 2011, at 17:51, Daron Ryan<daron.ryan@gmail.com> wrote: > >> Hello, >> >> I need to search a table to find sets of rows that have a column matching itself for the whole set and another columnmatching row for row with a list I am going to supply. The result I should receive should be value of the column thatmatches itself. >> >> For example given the following data in my table: >> >> 3; 1 >> 3; 2 >> 4; 8 >> 4; 9 >> 4; 10 >> >> I might need to search for 1,2. This should produce the result 3. Or if I were to search for 8, 9, 10 the result shouldbe 4. Searching for 8, 9 should produce an empty result as should 8, 9, 10, 11. >> >> Can anyone recommend a strategy? >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general
Le 18/06/2011 23:51, Daron Ryan a écrit :
>
> Hello,
>
> I need to search a table to find sets of rows that have a column
> matching itself for the whole set and another column matching row for
> row with a list I am going to supply. The result I should receive should
> be value of the column that matches itself.
>
> For example given the following data in my table:
>
> 3; 1
> 3; 2
> 4; 8
> 4; 9
> 4; 10
>
> I might need to search for 1,2. This should produce the result 3. Or if
> I were to search for 8, 9, 10 the result should be 4. Searching for 8, 9
> should produce an empty result as should 8, 9, 10, 11.
>
> Can anyone recommend a strategy?
>
this is a general case of relationnal division.
One way to do this is :
WITH
T0 AS
(SELECT 1 AS N --> all the data to be search each on a separate SELECT
UNION ALL --> with UNION ALL
SELECT 2 AS N)
SELECT TBL_ID
FROM T_MY_TABLE_TBL AS T
INNER JOIN T0
ON T.TBL_VALUE = T0.N
GROUP BY TBL_ID
HAVING COUNT(*) = (SELECT COUNT(*)
FROM T0);
I you read french, I wrote a paper on the relational division :
http://sqlpro.developpez.com/cours/divrelationnelle/
A +
--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************
[...]
> I need to search a table to find sets of rows that have a column
matching
> itself for the whole set and another column matching row for row with a
> list I am going to supply. The result I should receive should be
value of
> the column that matches itself.
[...]
How about:
DROP TABLE IF EXISTS T;
CREATE TABLE T
(
id int,
val int
);
INSERT INTO T (id, val) VALUES
(3, 1),
(3, 2),
(4, 8),
(4, 9),
(4, 10);
SELECT
id
FROM
T
GROUP BY
id
HAVING
array_agg(val) = ARRAY[8, 9, 10];