Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Getting back duplicate results

Author  Topic 

elektrobank
Starting Member

15 Posts
Posted - 2003-02-17 : 17:35:16
How can I get back multiple copies of the same results from a table? For instance if I have a table like this:

inventory:

ID|Description|Price
------------------------
1 | Item 1 | $15.00
2 | Item 2 | $18.00

and I want to be able to get back results with like 3 of item 1 and 2 of item 2, so the result set from my query would be like:

ID|Description|Price
------------------------
1 | Item 1 | $15.00
1 | Item 1 | $15.00
1 | Item 1 | $15.00
2 | Item 2 | $18.00
2 | Item 2 | $18.00

Or I may want to get back 5 of item 1 and 0 of item 2. How can I do this with a single query? All the work has to be done within the query, I can't build this set with code from the results or use temp tables or anything like that.
I just want to be able to get back multiple copies of the same item how ever many I want.

chadmat
The Chadinator

1974 Posts
Posted - 2003-02-17 : 18:00:37
select id, description, price
from table
where id=1
union
select id, description, price
from table
where id=1


-Chad
Go to Top of Page

elektrobank
Starting Member

15 Posts
Posted - 2003-02-17 : 18:03:58
I had already tried that. A union wont work since the results are the same so it only shows one of them.
quote:

select id, description, price
from table
where id=1
union
select id, description, price
from table
where id=1


-Chad
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-02-17 : 18:15:42
Do a cross join with a table of numbers or pretty much anything; that will multiply the # of records you return by the number of records in the table you cross join with.

That is, if you have a table called "Numbers" with 3 records,


SELECT id, description, price
FROM YourTable
CROSS JOIN
Numbers

will return 3 times as many records as there are in YourTable.

You could also set up a table with each item id, and how many of that item you'd like to return:

ID,ReturnCount
1,3
2,2
..etc...

Call the above table "ItemReturnCounts".

Then you can:

SELECT A.id, A.description, A.price
FROM YourTable A
INNER JOIN
ItemReturnCounts IRC
ON A.ID = IRC.ID
INNER JOIN
Numbers
ON Numbers.Number <= IRC.ReturnCount

Or something along those lines; that allows you to control how many records of each ID you'd like to return based on the values in the ItemReturnCounts table.

Finally, you could just put duplicate dummy records another table; call this "DummyTable":

ID
1
1
1
2
2
2

Then,

SELECT A.ID, A.Description, A.Price
FROM YourTable A
INNER JOIN DummyTable B
ON A.ID = B.ID

will return as many records for each ID as there are records for each ID in the DummyTable.



- Jeff
Go to Top of Page

chadmat
The Chadinator

1974 Posts
Posted - 2003-02-17 : 18:18:26
Sorry,

I meant "Union all"

select id, description, price
from table
where id=1
union all
select id, description, price
from table
where id=1

-Chad
Go to Top of Page

elektrobank
Starting Member

15 Posts
Posted - 2003-02-17 : 18:21:36
Yup, that's just what I needed, thanks!!!!

quote:

Sorry,

I meant "Union all"

select id, description, price
from table
where id=1
union all
select id, description, price
from table
where id=1

-Chad
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site