Jump to content

MS SQL Query Help?


Recommended Posts

Hello and thanks for your help.

 

I have a sports league database that contains 3 tables:

Team, Captain, and Player.

 

I am trying to order t-shirts for all captains and players for this league.

 

I did not create this database.  Here's the dilema;

The TEAM table contains the shirt color for the team (column name is 'shirtcolor')

The CAPTAIN table contains the team captain and his shirt size (column name is 'shirtsize')

and finally the PLAYER table (column name is 'pshirt') for the player shirt size.

 

What I need to do is create a query to gather and display all the shirts, broken down by color and size for both the captains and players.  The creator of the database wrote a query a while back for the players shirts that works.  Here is the code for the players shirts that works:

 

SELECT t.shirtcolor, p.pshirt, COUNT(t.shirtcolor) as shirt_count FROM team as t, player as p WHERE t.t_id = p.team AND t.locked = 1 GROUP BY t.shirtcolor, p.pshirt ORDER BY shirtcolor, pshirt

 

What I need to do is to come up with a query for ALL shirts (not just the players) and dawg-gone it, I just can't figure out how to do it. 

 

Can anyone help?

 

Thanks in advance!

 

 

Link to comment
Share on other sites

  • 2 weeks later...
  • 2 weeks later...

 

SELECT t.shirtcolor, p.pshirt, COUNT(t.shirtcolor) as shirt_count FROM team as t, player as p WHERE t.t_id = p.team AND t.locked = 1 GROUP BY t.shirtcolor, p.pshirt ORDER BY shirtcolor, pshirt

 

 

Hey, maybe it's a bit late now, but you could use a union - something like this:

SELECT t.shirtcolor, p.pshirt shirtsize, COUNT(t.shirtcolor) as shirt_count 
FROM 
team t
	JOIN player p ON t.t_id = p.team 
WHERE t.locked = 1
UNION
SELECT t.shirtcolor, c.shirtsize, COUNT(t.shirtcolor) as shirt_count 
FROM 
team t
	JOIN captain c ON t.t_id = c.team 
WHERE t.locked = 1
GROUP BY shirtcolor, shirtsize
ORDER BY shirtcolor, shirtsize

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.