CODEWITHSUNDEEP
sql
ojblass
ojblass

Reputation: 21620

Returning a row more than once in SQL from a single table?

Lets say I have a table CUSTOMERS with a key of CUSTOMER_ID. I would like to return multiple rows based on a query:

SELECT NAME FROM CUSTOMERS WHERE CUSTOMER_ID in (130,130);

This always returns a single row that matches although I would like it to appear twice. My motivation is a bit convoluted and far more complex than the tiny example above. The spreadsheet I would like to cut and paste this information into was the result of a complex join that I do not have access to. The list that I have has 43,000 records. I have done this in the past by unloading the row into a file and looping over my input list and grepping the output. Is there a neat SQL trick I could use?

Upvotes: 0

Views: 2221

Answers (3)

derobert
derobert

Reputation: 51157

Method 1:

You can UNION ALL your query to itself. That should double the rows.

    SELECT NAME FROM CUSTOMERS WHERE CUSTOMER_ID IN (130,131,...)
UNION ALL
    SELECT NAME FROM CUSTOMERS WHERE CUSTOMER_ID IN (130,131,...)

That ought to do it. I hope you have a good reason for this, because its fundamentally wrong.

Method 2:

Create a dummy table, with one column and two rows. Join it, without any restrictions, allowing the database to create the Cartesian product:

CREATE TABLE dummy ( foo INTEGER PRIMARY KEY );
INSERT INTO dummy VALUES (1);
INSERT INTO dummy VALUES (2);

SELECT NAME FROM CUSTOMERS JOIN dummy WHERE CUSTOMER_ID IN (130,131,...)

You can use the dummy table with a bunch of values 1..n to generate 1 through n copies of each row by changing the join criteria/where clause.

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562438

Here's one possible solution:

SELECT c.NAME FROM CUSTOMERS c 
JOIN (SELECT 130 AS CUSTOMER_ID UNION ALL SELECT 130) x 
  USING (CUSTOMER_ID);

Note that the syntax for selecting without naming a table is vendor-dependent. The example above works in MySQL and Microsoft SQL Server, for example, but not in Oracle.

You could also store the values 130, 130 as separate rows in a temporary table, and then join the temporary table to CUSTOMERS.

Upvotes: 1

dkretz
dkretz

Reputation: 37655

Whatever logic is generating the "IN (130, 130)" could presumably as easily generate

SELECT NAME FROM CUSTOMERS WHERE CUSTOMER_ID = 130
SELECT NAME FROM CUSTOMERS WHERE CUSTOMER_ID = 130

which would give it to you twice (and probably be easier to generate).

Upvotes: 1

Related Questions