1

I can't seem to figure out the best way to do this...

Table A                   Table B
    Id                    Id
    TableBId              ValueNeeded
                          TableAId

So I Table A needs values from Table B, where Table B's 'TableAId' is matching the id.

Example

TableA                   TableB
_________________________________________________________
ID        TableBId       Id     ValueNeeded    TableAId
1         ???            1      'Foo'          1
                         2      'Bar'          1

I need Table A to pull both value's from table B, ('Foo','Bar'). but the complicated part is that Table A can only have one column to return multiple values. And I foresee up to nearly 30 potential rows needed from Table B, for Table A. I think I need a lookup table in between, but does said lookup table need to have 30 columns? and what if a use case requires more than those 30 columns? I need this to work somehow

Edit: clarity - I'm creating a web app for myself and some friends to use for Dungeons and Dragons, this is for the 'Proficiencies' portion of the player character generation. Every player gets different proficiencies based on class, race, and level. So a level 1 player might only have 8-15 proficiencies, whereas a level 20 player could have dozens they've amassed. What I'm trying to accomplish is: One player(TableA) with ideally, one column, that points to a reference table(TableB), that then (ideally) has a few rows (race, class) that then pulls from another table (TableC) to finally fill out all the proficiencies that the player will have. The more I think about it the more sure I am that I need to make the reference table with a LOT of nullable columns....

  • It is hard to guess what you're trying to achieve here, it would help if you included some code that you already wrote attempting to solve this problem. Do you want to insert the primary key values from TableB into the TableBId field of TableA or are you trying to create a query that will show the values? – mendosi Nov 21 '16 at 0:22
  • 1
    look like just need select with LEFT JOIN - w3schools.com/sql/sql_join_left.asp – a_vlad Nov 21 '16 at 0:25
0

Every character has one and only one race, right? So it seems like that field should be on the Character table: TableA(?) Each character could have multiple classes so that should probably be in a one-to-many relationship in the CharacterClass table which has the primary key of both Character and Class tables.

As for the Proficiencies (TableB), each character can have zero to many proficiencies so they must be stored in their own table, but if you want to produce a 'report' which shows all the proficiencies as a delimited list, then you could do it like this:

Select c.id,
    proficiencies = Stuff((Select ',' + proficiency 
                             From #character_proficiency As cp
                             Where cp.character_id = c.Id
                             For XML Path ('')), 1, 1, '')
  From #character As c;
  • This is what I needed! thank you mendosi! – Spencer Nov 21 '16 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.