Search two numbers at once

Question

Maybe that question has even been answered already, but I don’t know how to do it, and I fell into the typical XY problem.

I have a table that has several ids, and I need to search two id’s, 34 e 5.

Sqlfiddle

In the above case, I created exactly what you will bring in my table, only I need you to only bring information when you have the two numbers 34 and 5 in the same transaction, not just one. So in this case the IN didn’t work.

Follows structure of the table:

CREATE TABLE IDS (
    IDTRANSACTIONS INT,
    IDSUBSTATUS INT
);

INSERT INTO IDS (IDTRANSACTIONS, IDSUBSTATUS)
VALUES (12548, 1),
(12548, 5),
(12548, 34),
(12548, 6),
(12548, 3),
(48754, 1),
(48754, 5),
(48754, 32),
(48754, 3),
(48754, 1),
(48754, 6)

And the select I’m making:

select * from ids where idsubstatus in (34,5)

and the result it is bringing:

IDTRANSACTIONS  IDSUBSTATUS
12548           5
12548           34
48754           5

IDTRANSACTION 12548 is correct, but not 48754. :)

Answer 1

You making a INNER JOIN searching for the idtransactions and the number sought in idsubstatus will force the SELECT to return only the items that have the two numbers:

SELECT a.idtransactions
FROM ids a
INNER JOIN ids b ON a.idtransactions = b.idtransactions AND b.idsubstatus = 34
INNER JOIN ids c ON a.idtransactions = c.idtransactions AND c.idsubstatus = 5
WHERE a.idsubstatus in (34, 5);

The WHERE will cause no lines to be returned that do not have the idsubstatus equal to 5 or 34;

In the format above, will be returned twice the idtransactions 12548, but you can bring only once grouping the result:

SELECT a.idtransactions
FROM ids a
INNER JOIN ids b ON a.idtransactions = b.idtransactions AND b.idsubstatus = 34
INNER JOIN ids c ON a.idtransactions = c.idtransactions AND c.idsubstatus = 5
GROUP BY a.idtransactions;

See more about JOIN here.

Answer 2

Another way of doing it would be using HAVING COUNT in subquery. This way, you don’t get trapped in only 2 values ( 34,5 ), can pass more parameters:

SELECT * 
FROM ids
WHERE idsubstatus IN (34,5)
AND idtransactions IN (SELECT idtransactions
FROM ids
WHERE idsubstatus IN (34,5)
GROUP BY idtransactions
HAVING COUNT(idtransactions) = 2)

Upshot:

---

Or in case you wanted to bring only the idtransactions:

SELECT idtransactions, GROUP_CONCAT(idsubstatus) AS idsubstatus
FROM IDS
WHERE idsubstatus IN (34,5)
GROUP BY idtransactions
HAVING COUNT(idtransactions) = 2

Upshot:

---

As in your example, but bringing all the status of the filtered record:

SELECT idtransactions, GROUP_CONCAT(idsubstatus) AS idsubstatus
FROM ids
WHERE idtransactions IN (SELECT idtransactions
FROM ids
WHERE idsubstatus IN (34,5)
GROUP BY idtransactions
HAVING COUNT(idtransactions) = 2)
GROUP BY idtransactions

Upshot:

Note that in the HAVING COUNT you will have to set the total amount of items in your condition idsubstatus IN (34,5).

---

Using subquery:

SELECT idtransactions, GROUP_CONCAT(idsubstatus)
FROM ids
WHERE idtransactions IN
  (SELECT idtransactions FROM ids WHERE idsubstatus = 34)
AND idtransactions IN
  (SELECT idtransactions FROM ids WHERE idsubstatus = 5)
GROUP BY idtransactions;

Upshot:

---

See working on db-fiddle

---

References

GROUP_CONCAT

HAVING