Thread: Search for lists

Search for lists

From
Daron Ryan
Date:
  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?

Re: Search for lists

From
David Johnston
Date:
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


Re: Search for lists

From
David Johnston
Date:
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

Re: Search for lists

From
Daron Ryan
Date:
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


Re: Search for lists

From
"F. BROUARD / SQLpro"
Date:
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 *************************


Re: Search for lists

From
Gavin Flower
Date:
[...]
 > 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];