Re: Search for lists - Mailing list pgsql-general

From F. BROUARD / SQLpro
Subject Re: Search for lists
Date
Msg-id 4DFE4892.1090004@club-internet.fr
Whole thread Raw
In response to Search for lists  (Daron Ryan <daron.ryan@gmail.com>)
List 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 *************************


pgsql-general by date:

Previous
From: "F. BROUARD / SQLpro"
Date:
Subject: Re: Referencing function value inside CASE..WHEN
Next
From: Cédric Villemain
Date:
Subject: Re: PostgreSQL 8.4.8 bringing my website down every evening