Repeating rows 1

[sacham]

I have three tables:
Deck:
DeckID
DeckName

DeckCard:
DeckID
CardID
Count

Card:
CardID
CardName

I want to write an SQL statement that returns a given Card row DeckCard.Count times. Is this possible?

Any help is appreciated,


sacha

 

[Cagliostro]

select DeckCard from DeckCard where CardID in(select CardID from Card where CardName = NameOfCardWhatYouNeed) John Fill

ivfmd@mail.md

 

[sacham]

Thanks for your reply, John, but that query will only return one row for each match. Perhaps I wasn't clear. If I have data:

Deck (DeckID, DeckName):
1 sacham's deck
2 your deck

DeckCard (DeckID, CardID, Count):
1 1 1
1 2 2
2 3 1
2 4 4

Card (CardID, CardName):
1 Ace
2 Jack
3 Seven
4 Ten

Then, conceptually, deck #1 has 3 cards in it: an ace and two jacks. Deck #2 has 5 cards in it: a seven and four tens. I need a query that will allow me to return a list of cards in a given deck. So, a query on deck #1 would return:
Ace
Jack
Jack

And on deck #2:
Seven
Ten
Ten
Ten
Ten


A kludgey way of doing this (and the way I'm doing it right now) would be to use UNION ALL, as follows (pseudo-SQL):

get all cards in decks that have a count >= 1
UNION ALL
get all cards in decks that have a count >= 2
UNION ALL
get all cards in decks that have a count >= 3
...etc

Unfortunately, this only works if you have a reasonably bounded count. I would like a general way to do this.


sacha

 

[Cagliostro]

select CardName, Count from DeckCard inner join Card on Card.CardID = DeckCard.CardID where DeckCard.DeckID in(select DeckID from Deck where DeckName=NameWhatYouAreLookingFor) John Fill

ivfmd@mail.md

 

[redlam]

Hi Sacham.
This is my weak attempt to get the exact results you requested. I'd be open for other options if I were you though...
I would create a stored procedure that reorganizes the cards in a temporary table (if you are using SQL2000, use the table datatype as shown below, otherwise, use tempdb) and spits them back out as you have defined above. Here's my code:
-----------------------------------------------------------
create procedure usp_get_cards
@deckid int
as

set nocount on

declare @cardquery table
(cardname varchar(25))
declare @cardcount tinyint
declare @cardid tinyint
declare cur_card cursor for
select cardid
from deckcard
where deckid = @deckid


open cur_card
fetch next from cur_card into @cardid
while @@fetch_status = 0
begin
set @cardcount = 1
while @cardcount <= (select [count] from deckcard where cardid = @cardid and deckid = @deckid)

begin
insert into @cardquery
select cardname
from deckcard dc inner join
card c on
dc.cardid = c.cardid
where dc.deckid = @deckid and
c.cardid = @cardid

set @cardcount = @cardcount + 1
end

fetch next from cur_card into @cardid

end

close cur_card
deallocate cur_card

select cardname from @cardquery
------------------------------------------------------------
then call the sp like this:
usp_get_cards 1
usp_get_cards 2