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 *************************
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
