# Belonging to a particular variety — and not to any other [sql]

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:
``````SELECT t1.id FROM table1 t1, table2 t2 WHERE t2.key NOT FULLY IN(SELECT id FROM table3)
``````
October 8th 19 at 03:55
October 8th 19 at 03:57
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

``````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.
In Udachniy response from Silver_Clash wrote more.
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
OK, we have MySQL, analytic functions of no, many let defined as
X (x,y) and Y (id). You can do this:

``````select X. x
from X left join Y on X. y = Y. id
group by X. x
having count(*) = count(Y. id)
``````

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
Thank you for the sensible thought. - Easton_Kessl commented on October 8th 19 at 04:06
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 :)
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. - Easton_Kessl 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. - bertha.Jaskolski commented on October 8th 19 at 04:05

Find more questions by tags MySQLSQL