Search for two numbers at a time

Question

Search for two numbers at a time

Navigation

#1 by (5 votes)
#2 by (3 votes)

4

Maybe this question has even been answered already, but I do not 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 and 5.

SQLFiddle

In the case above, I created exactly what it will bring in my table, except that I need only to bring information when I have both numbers 34 and 5 in the same transaction, not just one. So in this case the IN did not work.

Following table structure:

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 doing:

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

and the result you are bringing:

IDTRANSACTIONS  IDSUBSTATUS
12548           5
12548           34
48754           5

The IDTRANSACTION 12548 is correct, but the 48754 is not. :)

mysql

asked by anonymous 17.09.2018 / 14:18

2 answers

3

Another way to do this would be to use HAVING COUNT in subquery . This way, you do not get caught in only 2 values ( 34,5 ), you 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)

Result:

Orifyoujustwantedtogetidtransactions:

SELECTidtransactions,GROUP_CONCAT(idsubstatus)ASidsubstatusFROMIDSWHEREidsubstatusIN(34,5)GROUPBYidtransactionsHAVINGCOUNT(idtransactions)=2

Result:

Asinyourexample,butbringingallthestatusofthefilteredrecord:

SELECTidtransactions,GROUP_CONCAT(idsubstatus)ASidsubstatusFROMidsWHEREidtransactionsIN(SELECTidtransactionsFROMidsWHEREidsubstatusIN(34,5)GROUPBYidtransactionsHAVINGCOUNT(idtransactions)=2)GROUPBYidtransactions

Result:

NotethatinHAVINGCOUNTyouwillhavetosetthetotalamountofitemsinyouridsubstatusIN(34,5)condition.

Usingsubquery:

SELECTidtransactions,GROUP_CONCAT(idsubstatus)FROMidsWHEREidtransactionsIN(SELECTidtransactionsFROMidsWHEREidsubstatus=34)ANDidtransactionsIN(SELECTidtransactionsFROMidsWHEREidsubstatus=5)GROUPBYidtransactions;

Result:

Seeworkingat db-fiddle

References

GROUP_CONCAT

HAVING

17.09.2018 / 14:48

Bold "b" does not apply in a range Return results for all months in range

score 5 · Accepted Answer

If you make a INNER JOIN looking for the idtransactions and the searched number in idsubstatus it will force SELECT to return only the items that have both 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 rows that are not returned to be returned. % w / o% equal to 5 or 34;

In the above format, the idsubstatus idtransactions will be returned twice, but you can only bring it once by 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 12548 here .