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 array of 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(dot)ryan(at)gmail(dot)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 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?
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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 are 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 number of values match.

David J.

On Jun 18, 2011, at 17:51, Daron Ryan <daron(dot)ryan(at)gmail(dot)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 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?
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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 identifiers are 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 number of values match.
>
> David J.
>
>
> On Jun 18, 2011, at 17:51, Daron Ryan<daron(dot)ryan(at)gmail(dot)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 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?
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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 *************************