MS SQL Query Help? COUNT question

[vbcoach]

Hello all.  I could use some ms sql assistance.  You see, I have a sports league that has various tables.  I want to draw different information out of two tables; captain and player.  Every team has a captain and multiple players.  Each captain and player has shirt options.  They choose their own shirt size and color.  In the database under the captain’s table are columns for the tshirt description called “shirtsize”, and an associated TShirt ID number called “shirt_id”

 

Under the players table are similar columns; pshirt and pshirt_id, respectively.  I am not good with joins, so I could use some assistance getting a total shirt count of similar shirt_id’s from both tables.

 

Captain’s shirt_id + player’s pshirt_id = total shirt_id counts.  The results would look something like this:

 

   - Shirt -                                 (ID)      (Count)

Unisex Small Hot Pink            1          3

Unisex Medium Hot Pink        2          8

Unisex Large Hot Pink            3          6

Unisex XLarge Hot Pink         4          2

Unisex Small Kelly Green      7          1

Unisex Medium Kelly Green  8          7

Unisex Large Kelly Green      9          8

Unisex XLarge Kelly Green    10        2

Unisex 2XLarge Kelly Green  11        1

Unisex Small Light Blue          13        4

Unisex Medium Light Blue      14        8

Unisex Large Light Blue          15        10

 

 

I can write this statement for one table (i.e.captain’s table) but can’t seem to merge the two tables for total shirt count.  Anyone have any idea?

[Barand]

A captain is just another player, so why a separate table to complicate things. Just add a flag in the player table to indicate the captain. EG

       +--------------+                                                                                                   
       | team         |                                                                                                   
       +--------------+                                                                                                   
       | team_id (PK) |---+   +----------------+                                +---------------+                         
       | teamname     |   |   | player         |                                | shirt         |                         
       +--------------+   |   +----------------+                                +---------------+                         
                          |   | player_id (PK) |---+                        +---| shirt_id (PK) |                         
                          +--<| team_id        |   |                        |   | description   |                         
                              | playername     |   |                        |   +---------------+                         
                              | captain        |   |                        |                                             
                              +----------------+   |   +----------------+   |                                             
                                                   |   | player_shirt   |   |                                             
                                                   |   +----------------+   |                                             
                                                   +--<| player_id (PK) |   |                                             
                                                       | shirt_id (PK)  |>--+                                             
                                                       +----------------+                      

Note, the above will allow for multiple shirts per player. If there is only ever one each then lose the player_shirt table and put the shirt_id in the player table.

Edited April 1, 2016 by Barand

[vbcoach]

Is this a diagram of a "view"?  

 

Tried to create a database without the captain table, but a captain on one team can be a captain of other teams in other league nights, and still players on yet other teams.  Never found a good way around this.

[benanamen]

You would need a joiner table like player_shirt so there can be multiple players on multiple teams. Pretty basic database design. So it would be like players_to_teams table with team_id and player_id. This would allow an unlimited amount of players to be on an unlimited amount of teams.

 
 

Edited April 2, 2016 by benanamen

[vbcoach]

Well these are all great ideas, but this doesn't help me with my initial query

[Barand]

How can we advise on how to join unknown tables? You have given us no information about your current table table structure and column names so how can we advise on how to join them. You haven't even given your current query .

[maxxd]

Before you start trying to answer your initial query, you need to look at the underlying database structure - what you've told us about it certainly sounds like it's going to make life more difficult for you moving forward. If you restructure your data as Barand and benanamen have suggested, you'll find your SQL joins to be much more self-explanatory and the data you do receive much more usable. It'll then be two simple joins - one from player_shirt to player and one from player_shirt to shirt to get any number of shirts per player. As it stands now, you're locked in to one shirt per player, and you can't have a player on multiple teams, let alone a user that's a player on one team but captain of another. In order to satisfy your initial request to count the number of shirts, that then becomes a simple COUNT(*) AS num_shirts from the player_shirt table - no joins needed at all.