Hi Habr.

I lightly boiled my head, perhaps a simple task, but to mind nothing comes.

We have Many X, for example (1,2; 2,3,4; 7,9,1; 1,0), there are a number of Y, as in (1,2,3,4,5,6,7,8)

Is it possible to show X only what is fully present in Y? In the sample only (1,2; 2,3,4).

If simply making the same IN design, but not for each member (1 is in Y, then satisfies the condition), and across collections.

Pseudodistances:

I lightly boiled my head, perhaps a simple task, but to mind nothing comes.

We have Many X, for example (1,2; 2,3,4; 7,9,1; 1,0), there are a number of Y, as in (1,2,3,4,5,6,7,8)

Is it possible to show X only what is fully present in Y? In the sample only (1,2; 2,3,4).

If simply making the same IN design, but not for each member (1 is in Y, then satisfies the condition), and across collections.

Pseudodistances:

```
SELECT t1.id FROM table1 t1, table2 t2 WHERE t2.key NOT FULLY IN(SELECT id FROM table3)
```

asked October 8th 19 at 03:55

2 answers

answered on

Solution

Let's start with the understanding that X is the set of sets, and Y is a set of elements. Therefore, to compare you can (and should) Y and elements of X or elements of Y, and "elements of elements" X.

Further, we denote the element X as x. The fact that x is a subset of Y, on SQL can be expressed as

Further, getting back to your problem, we have to clarify how you have stored a lot of X. for Example, if one table X (set_id, element_id), then you can connect it with an external Y connection as above, and then filter the set is not fully contained in Y. this is well suited analytic functions if your DBMS supports them in the proper amount. Again clarification is required.

Further, we denote the element X as x. The fact that x is a subset of Y, on SQL can be expressed as

```
not exists (
select 1
from x left join Y on x.id = Y. id
where Y. id is null
)
```

Further, getting back to your problem, we have to clarify how you have stored a lot of X. for Example, if one table X (set_id, element_id), then you can connect it with an external Y connection as above, and then filter the set is not fully contained in Y. this is well suited analytic functions if your DBMS supports them in the proper amount. Again clarification is required.

answered on October 8th 19 at 03:59

Maybe something that works:

select t1.id from table t1 where t1.key NOT in (subquery transposing a column to a row using SYS_CONNECT_BY_PATH...) ???

the fact that the brackets do not immediately write, it is necessary to remember :)

select t1.id from table t1 where t1.key NOT in (subquery transposing a column to a row using SYS_CONNECT_BY_PATH...) ???

the fact that the brackets do not immediately write, it is necessary to remember :)

Like that messy you have ever written.

Solve step by step — first write a query to determine whether to include one set to another. Then write the query which selects all sets for which the first question gets 1. commented on October 8th 19 at 04:02

Solve step by step — first write a query to determine whether to include one set to another. Then write the query which selects all sets for which the first question gets 1. commented on October 8th 19 at 04:02

Easier probably would be so.

The result of GROUP_CONCAT() I get in console mysql

x | y

1 | 1,2

2 | 2,7

3 | 6,8

4 | 2,1

Deployed without grouping:

x | y

1 | 1

1 | 2

2 | 2

2 | 7

3 | 6

3 | 8

4 | 2

4 | 1

So, I have a list(retrieved by a different query) that are required to satisfy the members of the set Y to the current X. But I don't know how to check for membership them**all** without GROUP_CONCAT and clouds shitty string functions. NOT in works, if we have hit at least one y, x is in the sample, and it is not. commented on October 8th 19 at 04:05

The result of GROUP_CONCAT() I get in console mysql

x | y

1 | 1,2

2 | 2,7

3 | 6,8

4 | 2,1

Deployed without grouping:

x | y

1 | 1

1 | 2

2 | 2

2 | 7

3 | 6

3 | 8

4 | 2

4 | 1

So, I have a list(retrieved by a different query) that are required to satisfy the members of the set Y to the current X. But I don't know how to check for membership them

Thanks for the reply — really a mess in my head, even the question is formulated normally can't. - Easton_Kessl commented on October 8th 19 at 04:00

X (x,y) and Y (id). You can do this:

Explanation. Attachable external Y connection. as a result we have for each y or Y. line id or null no matter how many times this y nor met. Further grouped according to x, line with the elements of x are collapsed to a single line. As a result, each row corresponds to an element of the set X in the initial formulation). Now filter out nepodhodyaschee x; the criterion is that the total number of items in the group, count(*), is equal to the count Y, count(Y. id) counts only not null values. - bertha.Jaskolski commented on October 8th 19 at 04:03