CODEWITHSUNDEEP
mysql
theonlynewts
theonlynewts

Reputation: 121

Retain Duplicates in MySQL Results

My query looks like this with four numbers:

select * from accounts where number in (000208,000208,001106,001110);

and the results I'm getting look like this with three numbers, which makes sense because 000208 is in there twice:

number id
000208 904
001106 574
001110 1580

but I'm wondering if there is a way to get these results:

number id
000208 904
000208 904
001106 574
001110 1580

Everything I can find is about removing duplicates from tables and results, but I can't find anything about this scenario.

Upvotes: 0

Views: 32

Answers (4)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can also use temp tables

DROP TEMPORARY TABLE IF EXISTS ids;

CREATE TEMPORARY TABLE ids 
( 
 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, 
  number INTEGER, 
  PRIMARY KEY (`id`)
);

INSERT INTO ids (number) VALUES (1),(1),(2);

SELECT a.* 
  FROM ids i
  LEFT JOIN accounts a ON a.number = i.number;

Upvotes: 1

kcsoft
kcsoft

Reputation: 2947

There are 2 ways of doing this One is presented below using UNION to generate the required rows, the second option is by creating temporary tables and joining with that

SELECT *
FROM accounts
INNER JOIN (
SELECT 000208 AS number
  UNION ALL SELECT 000208
  UNION ALL SELECT 001106
  UNION ALL SELECT 001110
) a1 ON accounts.number = a1.number

Upvotes: 0

Nir Levy
Nir Levy

Reputation: 12953

Having the same item twice in your IN clause means nothing.

this query:

select * from accounts where number in (000208,000208,001106,001110);

is exactly like this one:

select * from accounts where number in (000208,001106,001110);

The way you'll get the same result twice does not depend on the query, but on the data inside the table- if you have it twice in your table (assuming the table allows duplication's), you will get it twice, either with 000208 once or twice in the query

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270583

You can do this with a left join:

select a.*
from (select 000208 as number union all
      select 000208 union all
      select 001106 union all
      select 001110
     ) n left jion
     accounts a
     on n.number = a.number;

Upvotes: 0

Related Questions