CODEWITHSUNDEEP
sql-servert-sqlselectinsert-into
Kathy Judd
Kathy Judd

Reputation: 737

INSERT INTO a table using a subquery

I'm working a query that I've never done and I'm stuck on how to proceed. I would like to be a single insert into command.

The pseudo code what I'm trying to do is like this:

For each player 
    insert into CurrentHand table by getting x number of cards needed 
       so all the players have 10 cards in their hands.

So if player 1 will get 7 new cards if he has 3 cards in their hands. Play 2 will get 5 new cards if he has 5 cards in their hands.

So far I've gotten to this select statement but it feels like I'm using the wrong approach.

DECLARE @MaxHandCount int
SET @MaxHandCount = 10

SELECT Player.PlayerId
    , (SELECT COUNT(1) FROM CurrentHand WHERE PlayerId = Player.PlayerId AND IsUsed = 0) AS CurrentHandCount
    , (@MaxHandCount - (SELECT COUNT(1) FROM CurrentHand WHERE PlayerId = Player.PlayerId AND IsUsed = 0)) AS NeededHandCount
    , CardId
FROM Player, AvailableCard
WHERE Cardid IN (SELECT CardId FROM CurrentHand WHERE IsUsed = 0)
ORDER BY PlayerId

The table structure looks like:

Player
  - PlayerId

AvailableCard
  - CardId
  - CardValue

CurrentHand
  - PlayerId
  - CardId
  - IsUsed

Thanks so much.

Upvotes: 0

Views: 584

Answers (3)

TomT
TomT

Reputation: 971

This was very interesting. Here is my solution to "deal" the needed cards. Please read the commends in the code. This only does the select but I believe you can figure out the insert yourself. Check out the fiddle too.

-- for each card in player's hand assign a sequence number
with cte_currenthand as
(
  select PlayerId, 
    rank() over(partition by PlayerId order by CardId) CardSeq
  from CurrentHand
  where IsUsed = 0
)
-- for each player generate a sequence 1..10
, cte_maxhand as
(
select p.PlayerId, x.seq
from Player p
cross join (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) x(seq)
)
-- get cards the players need and assign them new sequence numbers
-- basically cte_maxhand minus cte_currenthand
, cte_needed as
(
  select mh.PlayerId, row_number() over(order by mh.seq) seq
  from cte_maxhand mh
  left join cte_currenthand ch 
    on ch.CardSeq = mh.seq
    and ch.PlayerId = mh.PlayerId
  where ch.CardSeq is null
)
-- generate a random sequence on remaining cards
, cte_deal as
(
  select CardId, row_number() over(order by CHECKSUM(NewId())) seq
  from AvailableCard ac 
  where not exists (
    select *
    from CurrentHand ch
    where ch.CardId = ac.CardId
    )
)
-- deal the cards
select n.PlayerId, d.CardId
from cte_needed n
inner join cte_deal d on d.seq = n.seq

Upvotes: 1

sqlhdv
sqlhdv

Reputation: 254

DECLARE @MaxHandCount int
SET @MaxHandCount = 10

SELECT Player.PlayerId
    , Count(CardID) AS CurrentHandCount
    , @MaxHandCount - Count(CardID)  AS NeededHandCount
    , CardId
FROM Player
join CurrentHead on Player.PlayerID = CurrentHead.PlayerID
WHERE IsUsed = 0
Group by Player.PlayerID
ORDER BY PlayerId

Upvotes: 0

Ronald
Ronald

Reputation: 3

Sample table structure:

CREATE TABLE [dbo].[cards](
    [player] [nvarchar](20) NOT NULL,
    [number] [int] NOT NULL
    )

Sample data:

insert into cards values ('p1',3)
insert into cards values ('p2',5)
insert into cards values ('p3',4)
insert into cards values ('p4',2)

Insert:

insert into cards
select player,10-sum(number) as number
from cards
group by player

Upvotes: 0

Related Questions