MS SQL Query Help?

[vbcoach]

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!

 

 

[vbcoach]

I can't believe not one person can help me with this sql query?

[Ken2k7]

Well, it sure helps to post in the correct forum, wouldn't it? This is PHP coding help, not SQL.

[gwolgamott]

Run a second query and append your results for theplayers with the result from the captains.

[spambadger]

 

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