Jump to content

Archived

This topic is now archived and is closed to further replies.

vbcoach

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!

 

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

 

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

Share this post


Link to post
Share on other sites

×
×
  • 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.