SELECT WHERE…IN…OBTAINING DUPLICATED IDS

Issue

I have following query:

SELECT `id_customer`,`club`
FROM `customer`
WHERE `id_customer` IN (
2487,
2347,
2347
)
ORDER BY `id_customer` DESC;

And I obtain following results:

2487 -> Club A
2347 -> Club B

But I need to obtain:

2487 -> Club A
2347 -> Club B
2347 -> Club B

Because I’m going to copy these data to .csv file.

Could you help to me?

Thanks in advance.

Solution

The reason this isn’t working is because the IN clause doesn’t match each of the specified criteria individually. Using 2347 twice will not cause that row to be selected twice.

One way to accomplish this could be to use a temporary table to hold your customer criteria, and then JOIN on that to get the results:

CREATE TEMPORARY TABLE customerFilter (id_customer INT);

INSERT INTO customerFilter (id_customer) VALUES (2487), (2347), (2347);

SELECT  c.id_customer,
        c.club
  FROM  customer c
    INNER JOIN customerFilter cf ON cf.id_customer = c.id_customer
  ORDER BY c.id_customer DESC;

DROP TABLE customerFilter;

This uses the temporary table customerFilter to specify each ID that you want to retrieve a customer for. In this case, because the INNER JOIN is used, a row will be matched in customer for each row in customerFilter, therefore giving the repeated rows you are looking for.

Output:

2487   Club A
2347   Club B
2347   Club B

Answered By – Martin

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0