I have a payments_limits table with payments_id and payments_limits_types_id fields. When I execute the following queries:

 SELECT payments_id FROM payments_limits WHERE payments_limits_types_id = 1;

and

 SELECT payments_id FROM payments_limits WHERE payments_limits_types_id = 2;

In the resulting samples of both queries, the same number of fields is returned.

And I would like to know if these resulting samples are the same. How can this be done in one request?

  • And in your architecture it is generally possible that payments_limits_types_id be equal to two or more values? - Broouzer King
  • @BroouzerKing, yes - Ksenia
  • that is, you have the value payments_limits_types_id is that .. something like 1,2,3 ... or just duplicating the lines where in one line payments_limits_types_id = 1 and in the other is 2? - Broouzer King
  • And there can be two identical payments_id for one payments_limits_types_id And if so, then whether to consider the quantity when calculating a match - Mike
  • @Mike, no, there shouldn't be the same payment_id for one payments_limits_types_id - Ksenia

1 answer 1

If within one set there cannot be two identical values, then the difference in sets can be obtained by the following query:

 SELECT payments_id, count(*), max(payments_limits_types_id) FROM payments_limits WHERE payments_limits_types_id IN (1, 2) GROUP BY payments_id HAVING count(*)=1

count(*) must be equal to 2 if there are two entries and therefore the value is in both sets. At the same time, for entries that are found only in one of the sets for max(payments_limits_types_id) it will be possible to understand in which.

If you only need the fact of a match, you can make select count(*) from (запрос_выше) A , which will yield the number of discrepancies

If there can be several identical payment_id in a group, then each of them should be reduced to one record and get the number of identical ones, and then do the same as above, additionally checking the numbers (if needed):

 SELECT payments_id, max(cnt), min(cnt), max(payments_limits_types_id) FROM ( SELECT payments_limits_types_id, payments_id, count(*) as cnt FROM payments_limits WHERE payments_limits_types_id IN (1, 2) GROUP BY payments_limits_types_id, payments_id ) A GROUP BY payments_id HAVING count(*)=1 OR min(cnt)<>max(cnt)
  • Thanks, but can I still have an option if one payments_limits_types_id can have several identical payment_id? - Ksenia
  • @Ksenia Completed - Mike